Calendar Table by Year

/* Script by Marie Bayer (MarieTake3 [at] Yahoo.com)
Populates a table with year date brackets.  There is
    quite a bit of 'unnecessary' data but it matches
    all the other calendar tables in my schema.

It creates a single record based on the start
    year value passed.

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 follows 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.
*/
DECLARE
     @StartYear INT
    ,@StartDate DATETIME;

/* 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.CalYear
);

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

/* 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.CalYear
(
     CalStartDt
    ,CalEndDt
    ,CalDayOfWeekNumber
    ,CalDayNumber
    ,CalMonthNumber
    ,CalYearNumber
    ,CalDayName
    ,CalMonthName
    ,CalDayText
    ,CalMonthText
    ,CalYearText
    ,CalYearMonthText
    ,CalMonthYearText
    ,CalDate
)
SELECT  
     @StartDate AS CalStartDt
    ,DATEADD(MS, -1 ,CAST(DATEADD(YY, 1, @StartDate) AS DATETIME2(3))) AS CalEndDt -- Odd issue with DATETIME not taking off the microsecond.  Try it and see.
    ,DATEPART(DW, @StartDate) AS CalDayOfWeekNumber
    ,DATEPART(DD, @StartDate) AS CalDayNumber
    ,DATEPART(MM, @StartDate) AS CalMonthNumber
    ,DATEPART(YY, @StartDate) AS CalYearNumber
    ,DATENAME(DW, @StartDate) AS CalDayName
    ,DATENAME(MM, @StartDate) AS CalMonthName
    ,CASE WHEN 
        LEN(DATEPART(DD, @StartDate)) = 1 THEN '0' + CAST(DATEPART(DD, @StartDate) AS CHAR(1))
        ELSE CAST(DATEPART(DD, @StartDate) AS CHAR(2))
    END AS CalDayText
    ,CASE WHEN 
        LEN(DATEPART(MM, @StartDate)) = 1 THEN '0' + CAST(DATEPART(MM, @StartDate) AS CHAR(1))
        ELSE CAST(DATEPART(MM, @StartDate) AS CHAR(2))
    END AS CalMonthText
    ,CAST(DATEPART(YY, @StartDate) AS CHAR(4)) AS CalYearText
    ,CAST(DATEPART(YY, @StartDate) AS CHAR(4)) + '-' +
        CASE WHEN 
            LEN(DATEPART(MM, @StartDate)) = 1 THEN '0' + CAST(DATEPART(MM, @StartDate) AS CHAR(1))
            ELSE CAST(DATEPART(MM, @StartDate) AS CHAR(2))
        END AS CalYearMonthText
    ,DATENAME(MM, @StartDate) + CHAR(32) + DATENAME(YY, @StartDate) AS CalMonthYearText
    ,CAST(CAST(@StartDate AS DATE) AS CHAR(10)) AS CalDate

/* Table DDL
*/
/* Calendar Table

USE [tempdb]
GO

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

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[CalYear](
[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_CalYear] 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

*/