Now days we can see variety of calender controls to display on web page. We might not or rarely used t-SQL to generate calender.
This code is just to test our T-SQL logic.
Here is T_SQL code.
SET NOCOUNT ON
DECLARE @TempT TABLE (MM INT, YYYY INT)
INSERT @TempT(MM, YYYY) SELECT 02, 2009
INSERT @TempT(MM, YYYY) SELECT 03, 2010
;
WITH Numbers(LineNumber) AS
(
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9 UNION ALL
SELECT 10 UNION ALL
SELECT 11 UNION ALL
SELECT 12
)
, FirstAndLastDayOfMonth AS
(
SELECT CAST(CONVERT(VARCHAR,DATEADD(dd,-(DAY(CAST(CAST(YYYY AS VARCHAR) + '-' + CAST(MM AS VARCHAR) + '-' + '1' AS DATETIME))-1),CAST(CAST(YYYY AS VARCHAR) + '-' + CAST(MM AS VARCHAR) + '-' + '1' AS DATETIME)),101) AS DATETIME) AS FirstDay
,CAST(CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,CAST(CAST(YYYY AS VARCHAR) + '-' + CAST(MM AS VARCHAR) + '-' + '1' AS DATETIME)))),DATEADD(mm,1,CAST(CAST(YYYY AS VARCHAR) + '-' + CAST(MM AS VARCHAR) + '-' + '1' AS DATETIME))),101) AS DATETIME) AS LastDay
FROM @TempT
)
, YearMonthAndDays AS
(
SELECT
UPPER(DATENAME(MONTH, FirstDay)) + N' ' + DATENAME(YEAR, FirstDay) AS Header
,REPLICATE(N' ', 4 * ((CASE WHEN DATEPART(WEEKDAY, FirstDay) + @@DATEFIRST > 7 THEN DATEPART(WEEKDAY, FirstDay) + @@DATEFIRST - 7
ELSE DATEPART(WEEKDAY, FirstDay) + @@DATEFIRST
END) - 1)) +
SUBSTRING(N' 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31',
1, 4 * DAY(LastDay) + 1) + N' ' AS AllDays
,ROW_NUMBER() OVER(ORDER BY FirstDay) AS RowNum
FROM FirstAndLastDayOfMonth
)
, WeekHeader AS
(
SELECT 'Sun Mon Tue Wed Thu Fri Sat' AS DayNames
)
, Calendar AS
(
SELECT
CASE LineNumber
WHEN 1 THEN N'*=============================*'
WHEN 2 THEN N'|' + LEFT(REPLICATE(N' ', (29 - LEN(Header)) / 2) + Header + REPLICATE(N' ', 29), 29) + N'|'
WHEN 3 THEN N'|=============================|'
WHEN 4 THEN N'|' + N' ' + DayNames + N' ' + N'|'
WHEN 5 THEN N'|-----------------------------|'
WHEN 12 THEN N'*-----------------------------*'
ELSE N'|' + SUBSTRING(AllDays, 1 + (LineNumber - 6) * 28, 28) + N' ' + N'|'
END AS Outputlines
,RowNum
,LineNumber
FROM YearMonthAndDays
CROSS JOIN WeekHeader
CROSS JOIN Numbers
WHERE LineNumber < 6
OR LEN(SUBSTRING(AllDays, 1 + (LineNumber - 6) * 28, 28)) > 0
OR LineNumber = 12
)
SELECT Outputlines
FROM Calendar
ORDER BY RowNum, LineNumber