Pages

Tuesday, 8 May 2012

CTEs


For Dates in a calendar style report you could use the following to generate the dates.

   with mycte as

   ( select cast('2012-01-01' as datetime) DateValue

        union all

        select DateValue + 1

        from    mycte  

         where   DateValue + 1 < getdate())

   -- select DateValue

   -- from    mycte

   --OPTION (MAXRECURSION 0)

select DateValue,

Year(DateValue) as Year,

DATEPART(Quarter ,DateValue) as Quarter,

DATENAME( month,DateValue) as MonthName,

DATEPART(m ,DateValue)  as MonthNo,

day (DateValue) as Day

from    mycte

OPTION (MAXRECURSION 0)

No comments:

Post a Comment