Transpose date range into sql rows-CodePudding

I want to display all the dates between startdate and enddate into one row, technically there should be 649 rows. Been searching through web as to how to do it but failed to find one. Please help.

Declare @StartDate Date =‘2021-01-01  Declare @EndDate Date =‘2022-10-12

Result:

Date_R
2021-01-01
2021-01-02
2021-01-03
. . .
2022-10-12

CodePudding user response：

Declare @StartDate Date ='2021-01-01';
Declare @EndDate Date ='2022-10-12';

WITH DATES(DATED)AS
(
  SELECT @StartDate
    UNION ALL
  SELECT DATEADD(DD,1,D.DATED)
  FROM DATES AS D
  WHERE D.DATED<@EndDate
)
SELECT D.DATED
FROM DATES AS D
OPTION(MAXRECURSION 0)

Example with creating temp-table

Declare @StartDate Date ='2021-01-01';
Declare @EndDate Date ='2022-10-12';

IF OBJECT_ID(N'TEMPDB..#DATES',N'U')IS NOT NULL
     DROP TABLE #DATES;

  CREATE TABLE #DATES
  (
    DATED DATE
  );

 WITH DATES(DATED)AS
 (
   SELECT @StartDate
     UNION ALL
   SELECT DATEADD(DD,1,D.DATED)
   FROM DATES AS D
   WHERE D.DATED<@EndDate
 )
 INSERT #DATES(DATED)
  SELECT D.DATED
  FROM DATES AS D
  OPTION(MAXRECURSION 0)

 SELECT TOP 50* FROM #DATES;--just to show sample data

 DROP TABLE #DATES;--drop temp-table