OLAP Date Dimension Table Script

This query will build a table called “OLAP_DATE_DIMENSION”, based upon a starting and ending date. As you can see all of the work is done by the “DATENAME” and “DATEPART” functions in MS SQL Server. If you want information on using these, Query Analyzer for MS SQL Server 2000 is great! I’m not sure what happened with MS, but post MS SQL Server the built in help documents suck and your better of using Google.

** This query has a little hard coded logic for my specific company’s fiscal year (April – March), adjust accordingly.

-- =============================================
-- Build Dimension Date Table
-- =============================================
DECLARE @StartDate as smalldatetime, @EndDate as smalldatetime

SET @StartDate = '04/01/2010'
SET @EndDate = '03/31/2011'

BEGIN
SELECT
DATEPART(dy, @StartDate) as DAY_OF_YEAR,
CASE
WHEN DATENAME(qq,@StartDate)-1=0 THEN
4
ELSE
DATENAME(qq,@StartDate)-1
END AS FISCAL_PERIOD,
DATENAME(m,@StartDate) AS MONTH_DESC,
DATEPART(m,@StartDate) AS MONTH_NUM,
DATEPART(qq,@StartDate) AS QUARTER_NUM,
CONVERT(smalldatetime, CONVERT(CHAR(10),@StartDate,110)) AS SALES_DATE,
REPLACE(CONVERT(CHAR(10),@StartDate,06),' ','-') AS SALES_DATE_SPL,
DATEPART(yy,@StartDate) AS YEAR_NUM,
DATEPART(d,@StartDate) AS DAY_OF_MONTH,
CASE
WHEN DATEPART(m,@StartDate)< 4 THEN
DATENAME(yy,@StartDate)-1
ELSE
DATENAME(yy,@StartDate)
END AS FISCAL_YEAR,
CASE
WHEN DATEPART(m,@StartDate)>3 THEN
DATEPART(m,@StartDate)-3
ELSE
12-(3-DATEPART(m,@StartDate))
END AS FISCAL_MONTH
INTO OLAP_DATE_DIMENSION

SELECT @StartDate = @StartDate + 1

END

WHILE (@StartDate <= @EndDate)
    BEGIN
     BEGIN
     INSERT INTO OLAP_DATE_DIMENSION SELECT
     DATEPART(dy, @StartDate) as DAY_OF_YEAR,
     CASE
     WHEN DATENAME(qq,@StartDate)-1=0 THEN
     4
     ELSE
     DATENAME(qq,@StartDate)-1
     END AS FISCAL_PERIOD,
     DATENAME(m,@StartDate) AS MONTH_DESC,
     DATEPART(m,@StartDate) AS MONTH_NUM,
     DATEPART(qq,@StartDate) AS QUARTER_NUM,
         CONVERT(smalldatetime, CONVERT(CHAR(10),@StartDate,110)) AS SALES_DATE,
     REPLACE(CONVERT(CHAR(10),@StartDate,06),' ','-') AS SALES_DATE_SPL,
     DATEPART(yy,@StartDate) AS YEAR_NUM,
     DATEPART(d,@StartDate) AS DAY_OF_MONTH,
     CASE
     WHEN DATEPART(m,@StartDate)< 4 THEN
     DATENAME(yy,@StartDate)-1
     ELSE
     DATENAME(yy,@StartDate)
     END AS FISCAL_YEAR,
     CASE
     WHEN DATEPART(m,@StartDate)>3 THEN
     DATEPART(m,@StartDate)-3
     ELSE
     12-(3-DATEPART(m,@StartDate))
     END AS FISCAL_MONTH
     END
     SELECT @StartDate = @StartDate + 1
    END