NAV Date table in SQL-statement

kriki

And now that I am busy with recursion:

-- recursive CTE to generate numbers like the NAV date table

DECLARE @from AS DATE;
DECLARE @to AS DATE;

SET @from = '2009-01-16';
SET @to = '2009-03-20';


WITH DateTable ([The Date]) AS
(SELECT @to

 UNION ALL
  
 SELECT DATEADD(DAY,-1,[The Date])
 FROM DateTable
 WHERE DATEADD(DAY,-1,[The Date]) >= @from
)
SELECT *
FROM DateTable
ORDER BY [The Date]
OPTION (MAXRECURSION 0)

See also http://mibuso.com/blogs/kriki/2009/11/20/nav-date-table-in-sql-statement/