Generate Academic Calendar using MySQL

What?
We have a specific timetabling system for academic institutions and all our staff/students follow academic week numbers as opposed to calendar week numbers.

Why?
The aim of this article is to quickly generate a calendar for a full academic year for referencing by staff/students.

How?
Let us assume that we have a system holding a calendar in the following table (called "joes_weekstructure"):
copyraw
ID        AcademicSet       WeekNumber      StartDate 
--------- ----------------- --------------- ------------------
417       2012/2013         1               2012-07-16
418       2012/2013         2               2012-07-23
419       2012/2013         3               2012-07-30
...
467       2012/2013         51              2013-07-01
468       2012/2013         52              2013-07-08
  1.  ID        AcademicSet       WeekNumber      StartDate 
  2.  --------- ----------------- --------------- ------------------ 
  3.  417       2012/2013         1               2012-07-16 
  4.  418       2012/2013         2               2012-07-23 
  5.  419       2012/2013         3               2012-07-30 
  6.  ... 
  7.  467       2012/2013         51              2013-07-01 
  8.  468       2012/2013         52              2013-07-08 

I'll use this table to create the calendar.

Query #1: Works from command-line but may generate "Empty Query 1065"
copyraw
SELECT
	calendar.WeekNumber AS AcademicWeek,
	DATE_FORMAT(calendar.StartDate, '\%e-\%b-\%y') AS Mon,
	DATE_FORMAT(DATE_ADD(calendar.StartDate, INTERVAL 1 DAY), '\%e-\%b-\%y') AS Tue,
	DATE_FORMAT(DATE_ADD(calendar.StartDate, INTERVAL 2 DAY), '\%e-\%b-\%y') AS Wed,
	DATE_FORMAT(DATE_ADD(calendar.StartDate, INTERVAL 3 DAY), '\%e-\%b-\%y') AS Thu,
	DATE_FORMAT(DATE_ADD(calendar.StartDate, INTERVAL 4 DAY), '\%e-\%b-\%y') AS Fri,
	WEEKOFYEAR(calendar.StartDate) AS CalendarWeek
FROM
	joes_weekstructure calendar
WHERE
	calendar.SetID=(
		SELECT acyear.SetID 
		FROM joes_weekstructure acyear 
		WHERE NOW() >= acyear.StartDate 
		ORDER BY acyear.StartDate DESC 
		LIMIT 0,1
	)
ORDER BY
	calendar.weekNumber
  1.  SELECT 
  2.      calendar.WeekNumber AS AcademicWeek, 
  3.      DATE_FORMAT(calendar.StartDate, '\%e-\%b-\%y') AS Mon, 
  4.      DATE_FORMAT(DATE_ADD(calendar.StartDate, INTERVAL 1 DAY), '\%e-\%b-\%y') AS Tue, 
  5.      DATE_FORMAT(DATE_ADD(calendar.StartDate, INTERVAL 2 DAY), '\%e-\%b-\%y') AS Wed, 
  6.      DATE_FORMAT(DATE_ADD(calendar.StartDate, INTERVAL 3 DAY), '\%e-\%b-\%y') AS Thu, 
  7.      DATE_FORMAT(DATE_ADD(calendar.StartDate, INTERVAL 4 DAY), '\%e-\%b-\%y') AS Fri, 
  8.      WEEKOFYEAR(calendar.StartDate) AS CalendarWeek 
  9.  FROM 
  10.      joes_weekstructure calendar 
  11.  WHERE 
  12.      calendar.SetID=( 
  13.          SELECT acyear.SetID 
  14.          FROM joes_weekstructure acyear 
  15.          WHERE NOW() >= acyear.StartDate 
  16.          ORDER BY acyear.StartDate DESC 
  17.          LIMIT 0,1 
  18.      ) 
  19.  ORDER BY 
  20.      calendar.weekNumber 

Same Query but without using % (avoids MySQL Error 1065: Works within PHP scripts)
copyraw
SELECT 
      calendar.WeekNumber AS AcademicWeek, 
      CONCAT( 
           DAYOFMONTH(calendar.StartDate),  
           '-',  
           SUBSTR(MONTHNAME(calendar.StartDate), 1, 3),  
           '-',  
           YEAR(calendar.StartDate) 
      ) AS Mon, 
      CONCAT( 
           DAYOFMONTH(DATE_ADD(calendar.StartDate, INTERVAL 1 DAY)),  
           '-',  
           SUBSTR(MONTHNAME(DATE_ADD(calendar.StartDate, INTERVAL 1 DAY)), 1, 3),  
           '-',  
           YEAR(DATE_ADD(calendar.StartDate, INTERVAL 1 DAY)) 
      ) AS Tue, 
      CONCAT( 
           DAYOFMONTH(DATE_ADD(calendar.StartDate, INTERVAL 2 DAY)),  
           '-',  
           SUBSTR(MONTHNAME(DATE_ADD(calendar.StartDate, INTERVAL 2 DAY)), 1, 3),  
           '-',  
           YEAR(DATE_ADD(calendar.StartDate, INTERVAL 2 DAY)) 
      ) AS Wed, 
      CONCAT( 
           DAYOFMONTH(DATE_ADD(calendar.StartDate, INTERVAL 3 DAY)),  
           '-',  
           SUBSTR(MONTHNAME(DATE_ADD(calendar.StartDate, INTERVAL 3 DAY)), 1, 3),  
           '-',  
           YEAR(DATE_ADD(calendar.StartDate, INTERVAL 3 DAY)) 
      ) AS Thu, 
      CONCAT( 
           DAYOFMONTH(DATE_ADD(calendar.StartDate, INTERVAL 4 DAY)),  
           '-',  
           SUBSTR(MONTHNAME(DATE_ADD(calendar.StartDate, INTERVAL 4 DAY)), 1, 3),  
           '-',  
           YEAR(DATE_ADD(calendar.StartDate, INTERVAL 4 DAY)) 
      ) AS Fri, 
      WEEKOFYEAR(DATE_ADD(calendar.StartDate, INTERVAL 4 DAY)) AS CalendarWeek 
 FROM 
      joes_weekstructure calendar 
 WHERE 
      calendar.SetID=( 
           SELECT acyear.SetID  
           FROM joes_weekstructure acyear  
           WHERE NOW() >= acyear.StartDate  
           ORDER BY acyear.StartDate DESC  
           LIMIT 0,1 
      ) 
 ORDER BY 
      calendar.weekNumber
  1.  SELECT 
  2.        calendar.WeekNumber AS AcademicWeek, 
  3.        CONCAT( 
  4.             DAYOFMONTH(calendar.StartDate), 
  5.             '-', 
  6.             SUBSTR(MONTHNAME(calendar.StartDate), 1, 3), 
  7.             '-', 
  8.             YEAR(calendar.StartDate) 
  9.        ) AS Mon, 
  10.        CONCAT( 
  11.             DAYOFMONTH(DATE_ADD(calendar.StartDate, INTERVAL 1 DAY)), 
  12.             '-', 
  13.             SUBSTR(MONTHNAME(DATE_ADD(calendar.StartDate, INTERVAL 1 DAY)), 1, 3), 
  14.             '-', 
  15.             YEAR(DATE_ADD(calendar.StartDate, INTERVAL 1 DAY)) 
  16.        ) AS Tue, 
  17.        CONCAT( 
  18.             DAYOFMONTH(DATE_ADD(calendar.StartDate, INTERVAL 2 DAY)), 
  19.             '-', 
  20.             SUBSTR(MONTHNAME(DATE_ADD(calendar.StartDate, INTERVAL 2 DAY)), 1, 3), 
  21.             '-', 
  22.             YEAR(DATE_ADD(calendar.StartDate, INTERVAL 2 DAY)) 
  23.        ) AS Wed, 
  24.        CONCAT( 
  25.             DAYOFMONTH(DATE_ADD(calendar.StartDate, INTERVAL 3 DAY)), 
  26.             '-', 
  27.             SUBSTR(MONTHNAME(DATE_ADD(calendar.StartDate, INTERVAL 3 DAY)), 1, 3), 
  28.             '-', 
  29.             YEAR(DATE_ADD(calendar.StartDate, INTERVAL 3 DAY)) 
  30.        ) AS Thu, 
  31.        CONCAT( 
  32.             DAYOFMONTH(DATE_ADD(calendar.StartDate, INTERVAL 4 DAY)), 
  33.             '-', 
  34.             SUBSTR(MONTHNAME(DATE_ADD(calendar.StartDate, INTERVAL 4 DAY)), 1, 3), 
  35.             '-', 
  36.             YEAR(DATE_ADD(calendar.StartDate, INTERVAL 4 DAY)) 
  37.        ) AS Fri, 
  38.        WEEKOFYEAR(DATE_ADD(calendar.StartDate, INTERVAL 4 DAY)) AS CalendarWeek 
  39.   FROM 
  40.        joes_weekstructure calendar 
  41.   WHERE 
  42.        calendar.SetID=( 
  43.             SELECT acyear.SetID 
  44.             FROM joes_weekstructure acyear 
  45.             WHERE NOW() >= acyear.StartDate 
  46.             ORDER BY acyear.StartDate DESC 
  47.             LIMIT 0,1 
  48.        ) 
  49.   ORDER BY 
  50.        calendar.weekNumber 
For those of you who are familiar with this, I am simply using an alternative to the DATE_FORMAT() function in mySQL.

Should yield:
copyraw
AcademicWeek   Mon         Tue         Wed         Thu         Fri         CalendarWeek
-------------- ----------- ----------- ----------- ----------- ----------- ---------------
1              16-Jul-2012 17-Jul-2012 18-Jul-2012 19-Jul-2012 20-Jul-2012 29
2              23-Jul-2012 24-Jul-2012 25-Jul-2012 26-Jul-2012 27-Jul-2012 30
3              30-Jul-2012 31-Jul-2012 1-Aug-2012  2-Aug-2012  3-Aug-2012  31
...
51             1-Jul-2013  2-Jul-2013  3-Jul-2013  4-Jul-2013  5-Jul-2015  27
52	       8-Jul-2013  9-Jul-2013  10-Jul-2013 11-Jul-2013 12-Jul-2015 28
  1.  AcademicWeek   Mon         Tue         Wed         Thu         Fri         CalendarWeek 
  2.  -------------- ----------- ----------- ----------- ----------- ----------- --------------- 
  3.  1              16-Jul-2012 17-Jul-2012 18-Jul-2012 19-Jul-2012 20-Jul-2012 29 
  4.  2              23-Jul-2012 24-Jul-2012 25-Jul-2012 26-Jul-2012 27-Jul-2012 30 
  5.  3              30-Jul-2012 31-Jul-2012 1-Aug-2012  2-Aug-2012  3-Aug-2012  31 
  6.  ... 
  7.  51             1-Jul-2013  2-Jul-2013  3-Jul-2013  4-Jul-2013  5-Jul-2015  27 
  8.  52           8-Jul-2013  9-Jul-2013  10-Jul-2013 11-Jul-2013 12-Jul-2015 28 


This query in T-SQL (SQL Server 2008 R2) for CMIS Facility
copyraw
SELECT			
	calendar.WeekNumber AS AcademicWeek,		
	CAST(DATEPART(dd, calendar.StartDate) AS VARCHAR(2)) + '-' + SUBSTRING(DATENAME(mm, calendar.StartDate), 1, 3) + '-' + CAST(DATEPART(yyyy, calendar.StartDate) AS VARCHAR(4)) AS Monday,		
	CAST(DATEPART(dd, DATEADD(d, 1, calendar.StartDate)) AS VARCHAR(2)) + '-' + SUBSTRING(DATENAME(mm, DATEADD(d, 1, calendar.StartDate)), 1, 3) + '-' + CAST(DATEPART(yyyy, DATEADD(d, 1, calendar.StartDate)) AS VARCHAR(4)) AS Tuesday,		
	CAST(DATEPART(dd, DATEADD(d, 2, calendar.StartDate)) AS VARCHAR(2)) + '-' + SUBSTRING(DATENAME(mm, DATEADD(d, 2, calendar.StartDate)), 1, 3) + '-' + CAST(DATEPART(yyyy, DATEADD(d, 2, calendar.StartDate)) AS VARCHAR(4)) AS Wednesday,		
	CAST(DATEPART(dd, DATEADD(d, 3, calendar.StartDate)) AS VARCHAR(2)) + '-' + SUBSTRING(DATENAME(mm, DATEADD(d, 3, calendar.StartDate)), 1, 3) + '-' + CAST(DATEPART(yyyy, DATEADD(d, 3, calendar.StartDate)) AS VARCHAR(4)) AS Thursday,		
	CAST(DATEPART(dd, DATEADD(d, 4, calendar.StartDate)) AS VARCHAR(2)) + '-' + SUBSTRING(DATENAME(mm, DATEADD(d, 4, calendar.StartDate)), 1, 3) + '-' + CAST(DATEPART(yyyy, DATEADD(d, 4, calendar.StartDate)) AS VARCHAR(4)) AS Friday,		
	CAST(DATEPART(dd, DATEADD(d, 5, calendar.StartDate)) AS VARCHAR(2)) + '-' + SUBSTRING(DATENAME(mm, DATEADD(d, 5, calendar.StartDate)), 1, 3) + '-' + CAST(DATEPART(yyyy, DATEADD(d, 5, calendar.StartDate)) AS VARCHAR(4)) AS Saturday,		
	CAST(DATEPART(dd, DATEADD(d, 6, calendar.StartDate)) AS VARCHAR(2)) + '-' + SUBSTRING(DATENAME(mm, DATEADD(d, 6, calendar.StartDate)), 1, 3) + '-' + CAST(DATEPART(yyyy, DATEADD(d, 6, calendar.StartDate)) AS VARCHAR(4)) AS Sunday,		
	DATEPART(wk, CAST(DATEPART(dd, DATEADD(d, 6, calendar.StartDate)) AS VARCHAR(2)) + '-' + SUBSTRING(DATENAME(mm, DATEADD(d, 6, calendar.StartDate)), 1, 3) + '-' + CAST(DATEPART(yyyy, DATEADD(d, 6, calendar.StartDate)) AS VARCHAR(4))) AS CalendarWeek		
FROM			
	cmisuser.WEEKSTRUCTURE calendar	
/* specific to our calendars 	
WHERE			
	calendar.SetID=(		
		SELECT	TOP 1 acyear.SetID 
		FROM	cmisuser.WEEKSTRUCTURE acyear 
		WHERE	acyear.StartDate >= GETDATE()
		ORDER BY acyear.StartDate DESC)	
*/
ORDER BY			
	calendar.weekNumber
  1.  SELECT 
  2.      calendar.WeekNumber AS AcademicWeek, 
  3.      CAST(DATEPART(dd, calendar.StartDate) AS VARCHAR(2)) + '-' + SUBSTRING(DATENAME(mm, calendar.StartDate), 1, 3) + '-' + CAST(DATEPART(yyyy, calendar.StartDate) AS VARCHAR(4)) AS Monday, 
  4.      CAST(DATEPART(dd, DATEADD(d, 1, calendar.StartDate)) AS VARCHAR(2)) + '-' + SUBSTRING(DATENAME(mm, DATEADD(d, 1, calendar.StartDate)), 1, 3) + '-' + CAST(DATEPART(yyyy, DATEADD(d, 1, calendar.StartDate)) AS VARCHAR(4)) AS Tuesday, 
  5.      CAST(DATEPART(dd, DATEADD(d, 2, calendar.StartDate)) AS VARCHAR(2)) + '-' + SUBSTRING(DATENAME(mm, DATEADD(d, 2, calendar.StartDate)), 1, 3) + '-' + CAST(DATEPART(yyyy, DATEADD(d, 2, calendar.StartDate)) AS VARCHAR(4)) AS Wednesday, 
  6.      CAST(DATEPART(dd, DATEADD(d, 3, calendar.StartDate)) AS VARCHAR(2)) + '-' + SUBSTRING(DATENAME(mm, DATEADD(d, 3, calendar.StartDate)), 1, 3) + '-' + CAST(DATEPART(yyyy, DATEADD(d, 3, calendar.StartDate)) AS VARCHAR(4)) AS Thursday, 
  7.      CAST(DATEPART(dd, DATEADD(d, 4, calendar.StartDate)) AS VARCHAR(2)) + '-' + SUBSTRING(DATENAME(mm, DATEADD(d, 4, calendar.StartDate)), 1, 3) + '-' + CAST(DATEPART(yyyy, DATEADD(d, 4, calendar.StartDate)) AS VARCHAR(4)) AS Friday, 
  8.      CAST(DATEPART(dd, DATEADD(d, 5, calendar.StartDate)) AS VARCHAR(2)) + '-' + SUBSTRING(DATENAME(mm, DATEADD(d, 5, calendar.StartDate)), 1, 3) + '-' + CAST(DATEPART(yyyy, DATEADD(d, 5, calendar.StartDate)) AS VARCHAR(4)) AS Saturday, 
  9.      CAST(DATEPART(dd, DATEADD(d, 6, calendar.StartDate)) AS VARCHAR(2)) + '-' + SUBSTRING(DATENAME(mm, DATEADD(d, 6, calendar.StartDate)), 1, 3) + '-' + CAST(DATEPART(yyyy, DATEADD(d, 6, calendar.StartDate)) AS VARCHAR(4)) AS Sunday, 
  10.      DATEPART(wk, CAST(DATEPART(dd, DATEADD(d, 6, calendar.StartDate)) AS VARCHAR(2)) + '-' + SUBSTRING(DATENAME(mm, DATEADD(d, 6, calendar.StartDate)), 1, 3) + '-' + CAST(DATEPART(yyyy, DATEADD(d, 6, calendar.StartDate)) AS VARCHAR(4))) AS CalendarWeek 
  11.  FROM 
  12.      cmisuser.WEEKSTRUCTURE calendar 
  13.  /* specific to our calendars 
  14.  WHERE 
  15.      calendar.SetID=( 
  16.          SELECT    TOP 1 acyear.SetID 
  17.          FROM    cmisuser.WEEKSTRUCTURE acyear 
  18.          WHERE    acyear.StartDate >= GETDATE() 
  19.          ORDER BY acyear.StartDate DESC) 
  20.  */ 
  21.  ORDER BY 
  22.      calendar.weekNumber 
Category: MySQL :: Article: 453

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Related Articles

Joes Revolver Map

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin bc1qf6elrdxc968h0k673l2djc9wrpazhqtxw8qqp4

Ethereum:
Donate to Joel Lipman with Ethereum 0xb038962F3809b425D661EF5D22294Cf45E02FebF
© 2024 Joel Lipman .com. All Rights Reserved.