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"): 
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" 
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) 
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
For those of you who are familiar with this, I am simply using an alternative to the DATE_FORMAT() function in mySQL.

Should yield: 
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
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