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/
