Wednesday, July 2, 2014

T-SQL: Get a list of dates

Returns a list of dates from first of year to end of year
DECLARE @startofyear DATETIME, @endofyear DATETIME
SET @startofyear =   DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
SET @endofyear = DATEADD(yy, DATEDIFF(yy,0,getdate()) + 1, -1);

WITH sample AS (
  SELECT @startofyear  AS [Date]  
  UNION ALL
  SELECT DATEADD(dd, 1, [Date])
    FROM sample s
   WHERE DATEADD(dd, 1, [Date]) <=  @endofyear) 
SELECT * 
  FROM sample
  option (maxrecursion 370) –loop not more than 370, change to zero to infinite



No comments:

Post a Comment