Wednesday, 24 August 2016

MSSQL - How to generate range of Dates

There the text of VIEW to generate range of dates between 1.January 2000 and 31.December next year:

  1. IF OBJECT_ID(N'dbo.calendar_dates') IS NOT NULL
  2. DROP VIEW dbo.calendar_dates;
  3. GO
  4. /*
  5. http://yabele.blogspot.de/2016/08/how-to-generate-range-of-dates.html
  6. */
  7. CREATE VIEW dbo.calendar_dates
  8. AS
  9. WITH
  10. R10 AS (
  11. SELECT * FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS T(I)
  12. )
  13. SELECT TOP(DATEDIFF(DAY, '2000-01-01', CAST(YEAR(GETDATE()) + 1 AS CHAR(4)) + '-12-31'))
  14. date_value = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY (SELECT 1)), '1999-12-31')
  15. FROM
  16. R10 T1 CROSS JOIN R10 T2 CROSS JOIN R10 T3 CROSS JOIN R10 T4 CROSS JOIN R10 T5 CROSS JOIN R10 T6
  17. /*
  18. SELECT *
  19. FROM dbo.calendar_dates
  20. ORDER BY date_value;
  21. */
  22. ;