NAV Date table in SQL-statement

krikikriki Posts: 8,853Member, Moderator
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
Use the SEARCH,Luke! || No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
NAV TechDays 2019: 21 & 22 November 2019, Antwerp (Belgium)
Sign In or Register to comment.