SQL Server Calendar Table

mssql-calendar-table.sql

USE master
GO

IF OBJECT_ID('dbo.calendar') IS NOT NULL
  DROP TABLE dbo.calendar;

IF OBJECT_ID('dbo.fn_generate_calendar') IS NOT NULL 
  DROP FUNCTION dbo.fn_generate_calendar;
GO

CREATE TABLE dbo.calendar (
    calendar_date     DATE  NOT NULL
  , yyyy              INT   NOT NULL
  , mm                INT   NOT NULL
  , dd                INT   NOT NULL
  , day_of_week       INT   NOT NULL
  , day_of_year       INT   NOT NULL
  , is_weekday        BIT   NOT NULL
  , last_day_of_month INT   NOT NULL
  , month_end         DATE  NOT NULL
  , week_of_year      INT   NOT NULL
  , week_of_month     INT   NOT NULL
  , quarter           INT   NOT NULL
  , is_leap_year      BIT   NOT NULL
  , CONSTRAINT pk_calendar PRIMARY KEY (calendar_date ASC));
GO

CREATE FUNCTION dbo.fn_generate_calendar (
    @FromDate DATETIME
  , @NoDays   INT)
  /*
    Generates a calendar table with sequential day numbering (@FromDate = SeqNo 1).
    See RETURNS table (comments) for meaning of each column.
    Notes:       1) Max for NoDays is 65536, which runs in just over 2 seconds.
    
    Example calls to generate the calendar:
    1) Forward for 365 days starting today:
                DECLARE @Date DATETIME
                SELECT @Date = GETDATE()
                SELECT * 
                FROM dbo.GenerateCalendar(@Date, 365) 
                ORDER BY SeqNo;
    2) Backwards for 365 days back starting today:
                DECLARE @Date DATETIME
                SELECT @Date = GETDATE()
                SELECT * 
                FROM dbo.GenerateCalendar(@Date, -365) 
                ORDER BY SeqNo;
    3) For only the FromDate:
                DECLARE @Date DATETIME
                SELECT @Date = GETDATE()
                SELECT * 
                FROM dbo.GenerateCalendar(@Date, 1);
    4) Including only the last week days of each month:
                Note: Seq no in this case are as if all dates were generated
                DECLARE @Date DATETIME
                SELECT @Date = GETDATE()
                SELECT * 
                FROM dbo.GenerateCalendar(@Date, 365) 
                WHERE Last = 1 ORDER BY SeqNo;
  */
RETURNS TABLE
WITH SCHEMABINDING
AS
  RETURN
  --===== High speed code provided courtesy of SQL MVP Jeff Moden (idea by Dwain Camps)
  --===== Generate sequence numbers from 1 to 65536 (credit to SQL MVP Itzik Ben-Gen)
  WITH E1 (N) AS (
    SELECT 1
    UNION ALL
    SELECT 1
  ), --2 rows   
  E2 (N) AS (
    SELECT 1
    FROM    E1 AS a
          , E1 AS b
  ), --4 rows    
  E4  (N) AS (
    SELECT 1
    FROM    E2 AS a
          , E2 AS b
  ), --16 rows
  E8  (N) AS (
    SELECT 1
    FROM    E4 AS a
          , E4 AS b
  ), --256 rows
  E16 (N) AS (
    SELECT 1
    FROM    E8 AS a
          , E8 AS b
  ), --65536 rows
  cteTally  (N) AS (
    SELECT  TOP (ABS(@NoDays))
            ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM    E16
  )  
  SELECT  t.N AS SeqNo -- [SeqNo]=Sequential day number (@FromDate always=1) forward or backwards
        , dt.DT AS calendar_date -- [Date]=Date (with 00:00:00.000 for the time component)   
        , dp.YY AS yyyy -- [Year]=Four digit year                                                                                                       
        , dp.MM AS mm -- [Month]=Month (as an INT)                                    
        , dp.DD AS dd -- [Day]=Day (as an INT)                                                
        , DATEPART(dw, dt.DT) AS day_of_week -- [WkDNo]=Week day number (based on @@DATEFIRST)                                              
        , dp.Dy AS day_of_year -- [JulDay]=Julian day (day number of the year)                        
        , ISNULL (
            (
              CASE
                WHEN ((@@DATEFIRST-1)+(DATEPART (WeekDay, dt.DT)-1)) % 7 NOT IN (5,6) THEN 1
              END)
          ,0) AS is_weekday
        , DATEPART(dd, dp.LDtOfMo) AS last_day_of_month -- [LdOfMo]=Last day of the month             
        , dp.LDtOfMo AS month_end -- [LDtOfMo]=Last day of the month as a DATETIME
        , dp.Dy / 7 + 1 AS week_of_year -- [JulWk]=Week number of the year                                                                      
        , dp.DD / 7 + 1 AS week_of_month -- [WkNo]=Week number              
        , DATEPART(qq, dt.DT) AS quarter -- [Qtr]=Quarter number (of the year)   
        , ISNULL (
          (
            CASE
              WHEN dp.YY % 400 = 0 THEN 1
              WHEN dp.YY % 100 = 0 THEN 0
              WHEN dp.YY % 4 = 0 THEN 1
            END
          )
        , 0) AS is_leap_year
  FROM    cteTally AS t
          CROSS APPLY ( --=== Create the date
                      SELECT  DATEADD(dd, (t.N - 1) * SIGN(@NoDays), @FromDate) AS DT) AS dt
          CROSS APPLY ( --=== Create the other parts from the date above using a "cCA"
                      -- (Cascading CROSS APPLY (cCA), courtesy of Chris Morris)
                      SELECT  DATEPART(yy, dt.DT) AS YY
                            , DATEPART(mm, dt.DT) AS MM
                            , DATEPART(dd, dt.DT) AS DD
                            , DATENAME(dw, dt.DT) AS DW
                            , DATEPART(dy, dt.DT) AS Dy
                            , DATEADD(mm, DATEDIFF(mm, -1, dt.DT), -1) AS LDtOfMo) AS dp;
GO

INSERT  dbo.calendar (calendar_date, yyyy, mm, dd, day_of_week, day_of_year, is_weekday, last_day_of_month, month_end, week_of_year, week_of_month, quarter, is_leap_year)
SELECT  calendar_date
      , yyyy
      , mm
      , dd
      , day_of_week
      , day_of_year
      , is_weekday
      , last_day_of_month
      , month_end
      , week_of_year
      , week_of_month
      , quarter
      , is_leap_year
FROM    dbo.fn_generate_calendar('19000101', DATEDIFF(DAY, '19000101', '22001231'));
GO

CREATE NONCLUSTERED INDEX ix_calendar_yyyy ON dbo.calendar (yyyy);
CREATE NONCLUSTERED INDEX ix_calendar_mm ON dbo.calendar (mm);
CREATE NONCLUSTERED INDEX ix_calendar_dd ON dbo.calendar (dd);
CREATE UNIQUE NONCLUSTERED INDEX ux_calendar_yyyy_mm_dd ON dbo.calendar (yyyy, mm, dd);
GO

IF OBJECT_ID('dbo.fn_generate_calendar') IS NOT NULL 
  DROP FUNCTION dbo.fn_generate_calendar;
GO