Calendar Table by Year

,

This script will populate one record per year a calendar table with millisecond precision on the start and end dates.

The calendar table is wide and includes options for querying and displaying date information.

DDL is provided for the Calendar table.

/* 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

*/

Rate

2 (1)

Share

Share

Rate

2 (1)