DateDimension

select SUBSTRING(convert(nvarchar, FullDateAlternateKey, 120), 0, 5)+ SUBSTRING(convert(nvarchar, FullDateAlternateKey, 120), 6, 2)+ SUBSTRING(convert(nvarchar, FullDateAlternateKey, 120), 9, 2) as datekey, SUBSTRING(convert(nvarchar, FullDateAlternateKey, 120), 0, 5) as CalendarYear, CalendarQuarter = CASE CAST(SUBSTRING(convert(nvarchar, FullDateAlternateKey, 120), 6, 2) AS Int) WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 1 WHEN 4 THEN 2 WHEN 5 THEN 2 WHEN 6 THEN 2 WHEN 7 THEN 3 WHEN 8 THEN 3 WHEN 9 THEN 3 WHEN 10 THEN 4 WHEN 11 THEN 4 WHEN 12 THEN 4 END, CAST(SUBSTRING(convert(nvarchar, FullDateAlternateKey, 120), 6, 2) AS Int) as MonthNumber, CAST(SUBSTRING(convert(nvarchar, FullDateAlternateKey, 120), 9, 2) AS Int) as MonthDay, * from DateDimension Where FullDateAlternateKey is excel generated date format as 2010-01-01. update DateDimension set DateKey = SUBSTRING(convert(nvarchar, FullDateAlternateKey, 120), 0, 5)+ SUBSTRING(convert(nvarchar, FullDateAlternateKey, 120), 6, 2)+ SUBSTRING(convert(nvarchar, FullDateAlternateKey, 120), 9, 2), CalendarYear = SUBSTRING(convert(nvarchar, FullDateAlternateKey, 120), 0, 5), CalendarQuarter = CASE CAST(SUBSTRING(convert(nvarchar, FullDateAlternateKey, 120), 6, 2) AS Int) WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 1 WHEN 4 THEN 2 WHEN 5 THEN 2 WHEN 6 THEN 2 WHEN 7 THEN 3 WHEN 8 THEN 3 WHEN 9 THEN 3 WHEN 10 THEN 4 WHEN 11 THEN 4 WHEN 12 THEN 4 END, CalendarMonth = CAST(SUBSTRING(convert(nvarchar, FullDateAlternateKey, 120), 6, 2) AS Int), CalendarDay = CAST(SUBSTRING(convert(nvarchar, FullDateAlternateKey, 120), 9, 2) AS Int) from DateDimension