Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: 22527 | Views in the last 30 days: 102
 
Related Articles
FORUM

Index on BIT column

Index on BIT column

FORUM

Covering Index vs Included Columns

Is there any functional difference when querying

FORUM

user defined functions & columns

user defined functions & columns

FORUM

Creating a clustered index on Getdate function

Is it Ok to create a clustered index on a table containing a column with getdate function.

Tags
data warehousing    
datawarehouse    
datetime    
dimensions    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones