SQL script to convert Calendar

Hi.

Firstly, you can use UNPIVOT (SQL 2005+) to get the days vertically.

Something like:

;with cal_upvt(CalCentury, CalYear, CalMonth, CalDay, WorkDay) AS (
   -- unpivot your original data
   SELECT CalCentury, CalYear, CalMonth
        , CONVERT(INT, REPLACE(CalDay, 'CalDay', '')), WorkDay
   FROM Your_Calendar
   UNPIVOT(WorkDay FOR CalDay IN (CalDay1, CalDay2, CalDay3, CalDay4, CalDay31)) upvt
)
-- put together final date and display
SELECT (CalCentury*1000000+CalYear*10000+CalMonth*100+CalDay) AS [Date], WorkDay
FROM cal_upvt
;

Select allOpen in new window

OH, and if your [Date] column is ultimately to represent a *true* datetime, then just ensure that your data doesn't have any invalid options like CalMonth = 2 and CalDay31 <> null.  You can check for this by using ISDATE(CONVERT(CHAR(8), CalCentury*1000000+CalYear*10000+CalMonth*100+CalDay)) = 1 as a filter in WHERE of final select.

Getting error..
 ;WITH cal_upvt(CalCentury, CalYear, CalMonth, CalDay, WorkDay) AS (
   -- unpivot your original data
   SELECT CalCentury, CalYear, CalMonth
        , CONVERT(INT, REPLACE(CalDay, 'CalDay', '')), WorkDay
   FROM dbo.AdmCalendar
   UNPIVOT(WorkDay FOR CalDay IN (CalDay1, CalDay2, CalDay3,
   CalDay4,
   CalDay5,
   CalDay6,
   CalDay7,
   CalDay8,
   CalDay9,
   CalDay10,
   CalDay11,
   CalDay12,
   CalDay13,
   CalDay14,
   CalDay15,
   CalDay16,
   CalDay17,
   CalDay18,
   CalDay19,
   CalDay20,
   CalDay21,
   CalDay22,
   CalDay23,
   CalDay24,
   CalDay25,
   CalDay26,
   CalDay27,
   CalDay28,
   CalDay29,
   CalDay30,
   CalDay31) )

Msg 102, Level 15, State 1, Line 35
Incorrect syntax near ')'.

you are missing at least 1 ) at the end.

On a quick look, you did not run the whole query provided by kevin. run this.

;WITH cal_upvt(CalCentury, CalYear, CalMonth, CalDay, WorkDay) AS (
   -- unpivot your original data
   SELECT CalCentury, CalYear, CalMonth
        , CONVERT(INT, REPLACE(CalDay, 'CalDay', '')), WorkDay
   FROM dbo.AdmCalendar
   UNPIVOT(WorkDay FOR CalDay IN (CalDay1, CalDay2, CalDay3,
   CalDay4,
   CalDay5,
   CalDay6,
   CalDay7,
   CalDay8,
   CalDay9,
   CalDay10,
   CalDay11,
   CalDay12,
   CalDay13,
   CalDay14,
   CalDay15,
   CalDay16,
   CalDay17,
   CalDay18,
   CalDay19,
   CalDay20,
   CalDay21,
   CalDay22,
   CalDay23,
   CalDay24,
   CalDay25,
   CalDay26,
   CalDay27,
   CalDay28,
   CalDay29,
   CalDay30,
   CalDay31)) upvt
)
-- put together final date and display
SELECT (CalCentury*1000000+CalYear*10000+CalMonth*100+CalDay) AS [Date], WorkDay
FROM cal_upvt;

Select allOpen in new window