Calendar Table by Day

/* Script by Marie Bayer (MarieTake3 [at] Yahoo.com)
Populates a table with day date brackets

It creates a full year of dates based on the start
    year value passed.  Manage data in year sets!

The script pulls from the last date in the table
    and does ~not~ look for gaps.

I created mine in tempdb so they're available everywhere.

The DDL for the table and a tally table follow the code
    section.
*/
USE [tempdb]
GO

/* The variable @StartYear can also be a stored procedure
    parameter if desired.  I created mine to run without
    the option.

@Interval needs to match the value setting @End.
*/
DECLARE
     @StartYear INT
    ,@StartDate DATETIME
    ,@End INT;

/* The next two statements are not necessary if you're passing the
    value either as a full @StartDate or @StartYear.

The existing will build on the last value in the
    source table.
*/
SET @StartYear = (
    SELECT ISNULL(MAX(DATEPART(YY, CalDate)), 2009) + 1
    FROM dbo.CalDay
);

SET @StartDate = CAST(CAST(@StartYear AS CHAR(4)) + '-01-01' AS DATETIME);

SET @End = (
    SELECT DATEPART(DD, DATEADD(DD, -1, DATEADD(YY, 1, @StartDate)))
);

/* DDL for this table follows.  All my calendar tables have the exact same
    format for portability when the code needs to change from one interval
    to another.
*/
INSERT INTO dbo.CalDay
(
     CalStartDt
    ,CalEndDt
    ,CalDayOfWeekNumber
    ,CalDayNumber
    ,CalMonthNumber
    ,CalYearNumber
    ,CalDayName
    ,CalMonthName
    ,CalDayText
    ,CalMonthText
    ,CalYearText
    ,CalYearMonthText
    ,CalMonthYearText
    ,CalDate
    )
SELECT  
     DATEADD(DD, (N - 1), @StartDate) AS CalStartDt
    ,DATEADD(MS, -1 ,CAST(DATEADD(DD, N, @StartDate) AS DATETIME2(3))) AS CalEndDt -- Odd issue with DATETIME not taking off the microsecond.  Try it and see.
    ,DATEPART(DW, DATEADD(DD, (N - 1), @StartDate)) AS CalDayOfWeekNumber
    ,DATEPART(DD, DATEADD(DD, (N - 1), @StartDate)) AS CalDayNumber
    ,DATEPART(MM, DATEADD(DD, (N - 1), @StartDate)) AS CalMonthNumber
    ,DATEPART(YY, DATEADD(DD, (N - 1), @StartDate)) AS CalYearNumber
    ,DATENAME(DW, DATEADD(DD, (N - 1), @StartDate)) AS CalDayName
    ,DATENAME(MM, DATEADD(DD, (N - 1), @StartDate)) AS CalMonthName
    ,CASE WHEN 
        LEN(DATEPART(DD, DATEADD(DD, (N - 1), @StartDate))) = 1 THEN '0' + CAST(DATEPART(DD, DATEADD(DD, (N - 1), @StartDate)) AS CHAR(1))
        ELSE CAST(DATEPART(DD, DATEADD(DD, (N - 1), @StartDate)) AS CHAR(2))
    END AS CalDayText
    ,CASE WHEN 
        LEN(DATEPART(MM, DATEADD(DD, (N - 1), @StartDate))) = 1 THEN '0' + CAST(DATEPART(MM, DATEADD(DD, (N - 1), @StartDate)) AS CHAR(1))
        ELSE CAST(DATEPART(MM, DATEADD(DD, (N - 1), @StartDate)) AS CHAR(2))
    END AS CalMonthText
    ,CAST(DATEPART(YY, DATEADD(DD, (N - 1), @StartDate)) AS CHAR(4)) AS CalYearText
    ,CAST(DATEPART(YY, DATEADD(DD, (N - 1), @StartDate)) AS CHAR(4)) + '-' +
        CASE WHEN 
            LEN(DATEPART(MM, DATEADD(DD, (N - 1), @StartDate))) = 1 THEN '0' + CAST(DATEPART(MM, DATEADD(DD, (N - 1), @StartDate)) AS CHAR(1))
            ELSE CAST(DATEPART(MM, DATEADD(DD, (N - 1), @StartDate)) AS CHAR(2))
        END AS CalYearMonthText
    ,DATENAME(MM, DATEADD(DD, (N - 1), @StartDate)) + CHAR(32) + DATENAME(YY, DATEADD(DD, (N - 1), @StartDate)) AS CalMonthYearText
    ,CAST(CAST(DATEADD(DD, (N - 1), @StartDate) AS DATE) AS CHAR(10)) AS CalDate
FROM dbo.Tally AS T
WHERE N < @End + 1;

/* Table DDL
*/
/* Tally Table

USE [tempdb]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tally]') AND type in (N'U'))
DROP TABLE [dbo].[Tally]
GO

CREATE TABLE dbo.Tally (N INT, CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N));

DECLARE 
    @Counter INT = 1;

WHILE @Counter <= 100000  
BEGIN
                 
    INSERT INTO dbo.Tally (
        N
    )
    VALUES (
        @Counter
    );

    SET @Counter = @Counter + 1;
               
END;

*/
/* Calendar Table

USE [tempdb]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CalDay]') AND type in (N'U'))
DROP TABLE [dbo].[CalDay]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[CalDay](
[CalID] [int] IDENTITY(1,1) NOT NULL,
[CalStartDt] [datetime2](3) NULL,
[CalEndDt] [datetime2](3) NULL,
[CalDayOfWeekNumber] [int] NULL,
[CalDayNumber] [int] NULL,
[CalMonthNumber] [int] NULL,
[CalYearNumber] [int] NULL,
[CalDayName] [varchar](10) NULL,
[CalMonthName] [varchar](10) NULL,
[CalDayText] [char](2) NULL,
[CalMonthText] [char](2) NULL,
[CalYearText] [char](4) NULL,
[CalYearMonthText] [char](7) NULL,
[CalMonthYearText] [varchar](15) NULL,
[CalDate] [date] NULL,
 CONSTRAINT [PK_CalDay] PRIMARY KEY CLUSTERED 
(
[CalID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

*/