Create Date list (variable dates) - SQL Server, Sql Server

Create Date list (variable dates) - SQL Server

--------------------------------------------------------------------------------
-- Custom Date Range
-- Using Number sequence
-- Note that this limits us to 999 days
-- To add more days extend the CROSS JOIN
--------------------------------------------------------------------------------

DECLARE @DateStart DATE = '01-JAN-17'; -- At time of writing this is the past
DECLARE @DateEnd   DATE = '01-JAN-19'; -- At time of writing this is the future
 
PRINT 'Start Date: ' + CONVERT(VARCHAR(10), @DateStart, 120);
PRINT 'Start End: '  + CONVERT(VARCHAR(10), @DateEnd,   120);

WITH number_tbl AS (
SELECT 0 AS a UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9
), number_sequences AS (
SELECT t1.a AS t1a,
       t2.a AS t2a,
       t3.a AS t3a,
       (t3.a + (10 * t2.a) + (100 * t1.a)) AS concatenate_calculation
  FROM number_tbl t1
  CROSS JOIN number_tbl t2
  CROSS JOIN number_tbl t3
)
SELECT CAST(DateAdd(DAY, concatenate_calculation, @DateStart) AS DATE) AS [TheDate]
  FROM number_sequences
 WHERE CAST(DateAdd(DAY, concatenate_calculation, @DateStart) AS DATE) <= @DateEnd
 ORDER BY concatenate_calculation
;


run \| edit \| history \| help	0