NAV Date table in SQL-statement

krikikriki Member, Moderator Posts: 9,110
edited 2009-11-20 in SQL Tips & Tricks
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/
Regards,Alain Krikilion
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


Sign In or Register to comment.