SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Date and Time dimension creation and population T-SQL

By Joshua A. Walker,

Dim_Date and Dim_Time, Date and Time dimensions. Creation, population, indexing and use script.

Just copy the code into a query window and run the script.

There are no additional functions or procedures required.

This code will create the tables, populate the date and time tables, add holidays, add indexes and there are a few code snippets at the bottom for using them.

There are two variables in the code right after the table creation "@StartDate" and "@EndDate". Set the @StartDate to the first day you wish to use in your dim_Date table. By default this is 1/1/1900. Then set the @EndDate to the day after the last day you want to include. By default this is 1/1/2050. Every date from 1/1/1900 through 12/31/2049 will be included in your data.

The "ID" field on the dim_Date table is the primary key and the date as format YYYYMMDD. There are lines to make it an auto incrementing int that will start at 60,000 if you wish to follow the Kimball DW book that suggest not using a date format as your primary key.

The Date and Time columns in the tables are set up as character fields. This is the proper way to do this for reporting purposes. All day,month, hour, minute and second columns will always be two characters '01' vs. 1...etc...

All columns are indexed after the population.

For functions for these tables that will return the keys from the date and time tables when passed a datetime... and a function for returning the datetime when a key is passed... go here: http://www.sqlservercentral.com/scripts/Data+Warehouse/65897/

Total article views: 25174 | Views in the last 30 days: 8
Related Articles

Index on BIT column

Index on BIT column


Reclaiming Space After Column Data Type Change

A brief overview of how to reclaim storage space after altering the data type of a column.


Covering Index vs Included Columns

Is there any functional difference when querying


user defined functions & columns

user defined functions & columns

data warehousing