Friday, August 5, 2011

Generating Calendar in SQL Server 2008

Here is the simple query that generates a calendar.

  1: DECLARE @startDay DATE = '20110101'
  2:        ,@endDay DATE = '20130101'
  3: 
  4: ;WITH calendar ( [Date], [Year], [Quarter], [Month], [Day], [DayOfWeek], [MonthName], [DayName], [WeekNumber], isWeekDay )
  5: AS ( SELECT @startDay
  6:            ,YEAR(@startDay)
  7:            ,DATEPART(qq, @startDay)
  8:            ,DATEPART(mm, @startDay)
  9:            ,DATEPART(dd, @startDay)
 10:            ,DATEPART(dw, @startDay)
 11:            ,DATENAME(month, @startDay)
 12:            ,DATENAME(dw, @startDay)
 13:            ,DATEPART(wk, @startDay)
 14:            ,CASE WHEN DATEPART(dw, @startDay) IN ( 1, 7 ) THEN 0 ELSE 1 END
 15:    UNION ALL
 16:    SELECT  DATEADD(dd, 1,[date])
 17:            ,YEAR(DATEADD(dd, 1,[date]))
 18:            ,DATEPART(qq, DATEADD(dd, 1,[date]))
 19:            ,DATEPART(mm, DATEADD(dd, 1,[date]))
 20:            ,DATEPART(dd, DATEADD(dd, 1,[date]))
 21:            ,DATEPART(dw, DATEADD(dd, 1,[date]))
 22:            ,DATENAME(month, DATEADD(dd, 1,[date]))
 23:            ,DATENAME(dw, DATEADD(dd, 1,[date]))
 24:            ,DATEPART(wk, DATEADD(dd, 1,[date]))
 25:            ,CASE WHEN DATEPART(dw, DATEADD(dd, 1,[date])) IN ( 1, 7 ) THEN 0 ELSE 1 END
 26:    FROM     calendar
 27:    WHERE    DATEADD(dd, 1,[date]) < @endDay )
 28: SELECT *
 29: FROM   calendar
 30: OPTION  ( MAXRECURSION 1000 )
 31: 
 32: 

0 comments:

Post a Comment