Script to generate calender for a month

by Amol 29. April 2010 12:14
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

Tags:

SQL Server Scripts

Category

Recent Posts

Tag cloud