Tuesday, September 24, 2013

Script I used to populate DimDate from AdventureWorks with current data

WITH mycte AS
    
(
    
SELECT CAST('2012-01-01' AS DATETIME) DateValue
        
UNION ALL
    
SELECT DateValue + 1
        
FROM mycte
        
WHERE DateValue + 1 < '2013-12-31'
    
)INSERT INTO dimdate
    
SELECT CONVERT(VARCHAR, DateValue, 112) AS DateKey
            
, CAST(DateValue AS date) AS FullDateAlternateKey
            
, DATEPART(dw, datevalue) AS DayNumberOfWeek
            
, [dbo].[FormatDateTime] (DateValue, '[dweek]') AS DayNameOfWeek
            
, dbo.FormatDateTime(DateValue, '[d]') AS DayNumberOfMonth
            
, DATEPART(dy, datevalue) AS DayNumberOfYear
            
, DATEPART(ww, datevalue) AS WeekNumberOfYear
            
, dbo.FormatDateTime(DateValue, '[MONTH]') AS MonthName
            
, dbo.FormatDateTime(DateValue, '[M]') AS MonthNumberOfYear
            
, DATEPART(q, datevalue) AS CalendarQuarter
            
, DATEPART(yyyy, datevalue) AS CalendarYear
    
FROM mycte
            
OPTION (MAXRECURSION 0)


I'll include a new script that will populate the other tables with good dates some time later.

No comments:

Post a Comment