Wednesday 24 November 2010

Create a CTE list of dates on the fly - SQL

CTE list of dates on the fly:

WITH D AS
(
SELECT number AS rn FROM master.dbo.spt_values
WHERE type = 'P' AND number BETWEEN 0 AND 29
)
SELECT DATEADD(dd, -D.rn, DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)) AS ReportDate
FROM D;


2000/pre CTE solution here

No comments: