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]
    (PRIMARY KEY ([calendar_date])
  , [calendar_date]     DATETIME  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]         DATETIME  NOT NULL
  , [week_of_year]      INT       NOT NULL
  , [week_of_month]     INT       NOT NULL
  , [quarter]           INT       NOT NULL
  , [is_leap_year]      BIT       NOT NULL);
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]('1900-01-01', 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