In Land use in our Data Warehouse Dimension Date integers as primary keys for the date. So we skip the date "01/12/2008 00:00:00.000" as "20081201". This allows you to quickly choose a fast fact table join to submit your date dimension, joins on integers simply do faster than joins to datetime fields.

Dates are often stored in source systems in the DateTime format. We will thus have to convert DateTime to an integer. Do this with the following T-SQL statement:

  ( GETDATE ( ) ) * 10000 + MONTH ( GETDATE ( ) ) * 100 + DAY ( GETDATE ( ) ) AS DatetimeToInt SELECT YEAR (GETDATE ()) * 10000 + MONTH (GETDATE ()) * 100 + DAY (GETDATE ()) AS DatetimeToInt

GETDATE () you replace the date field you want to convert to an integer.

Another method is the following, which is slightly slower in practice:

  ( VARCHAR , DATEADD ( yy, -2 , GETDATE ( ) ) , 112 ) SELECT CONVERT (VARCHAR, DateAdd (yy, -2, GETDATE ()), 112) 

You can also imagine that you want to use this field as a normal date field, such as a report in Reporting Services. This can be easily achieved by making the following function:

  [ dbo ] . [ FromDateIDtoDateValue ] ( @ DATE VARCHAR ( 8 ) ) CREATE FUNCTION [dbo]. [FromDateIDtoDateValue] (@ DATE VARCHAR (8))
  RETURNS DATETIME
  AS

  @FunctionResult DATETIME DECLARE @ DATETIME Function Result

  ISDATE ( @ DATE ) = 1 SET @FunctionResult = CAST ( @ DATE AS DATETIME ) ISDATE IF (@ DATE) = 1 SET @ Result = CAST Function (@ DATE DATETIME AS)
  @FunctionResult = null ELSE SET @ Result Function = null

  ( @FunctionResult ) RETURN (@ Result Function)
  END 

You can then call this function:

  SELECT
  ( 20081201 ) dbo. FromDateIDtoDateValue (20081201)