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
- 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
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
- 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
Same Query but without using % (avoids MySQL Error 1065: Works within PHP scripts)
copyraw
For those of you who are familiar with this, I am simply using an alternative to the DATE_FORMAT() function in mySQL.
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
- 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
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
- 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
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
- 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
Category: MySQL :: Article: 453