Getting individual dates from a date range using T-SQL-CodePudding

I have been asked to create two datasets showing 7 days of dates from a two date range.

Example: I have a date range of StartDate = 2022-12-12 and EndDate = 2022-12-25. I need a query to display the individual dates in between these two dates. I was told to use DATEADD, but cannot for the life figure this out.

Any help would be be helpful, thank you.

SELECT DATEADD(DAY, 7, StartDate) AS WeekOne

I was expecting something like this:

CodePudding user response：

DECLARE @InStartDate DATE='2022-12-12';
DECLARE @InStopDate DATE='2022-12-25';
WITH GEN AS
(
   SELECT @InStartDate AS Start_dated
      UNION ALL
   SELECT DATEADD(DD,1,G.Start_dated)
     FROM GEN AS G
   WHERE G.Start_dated< @InStopDate
)
SELECT G.*
FROM GEN AS G

You can use something like this

CodePudding user response：

You need to start by generating a numbers table. It needs enough rows to handle each day between your start and end dates. Something like this:

with Numbers as (
    select 0 as n
    union all
    select n   1
    from Numbers
    where n < 365
)
select n
from Numbers
option(maxrecursion 0);

Given the example range, I felt like 365 days (one year) was adequate, but it's easy to tweak that range (as we'll see).

Once you have the Numbers table, you can use DateAdd() to add that amount to the start date:

DECLARE @StartDate date = '20221212'; 

with Numbers as (
    select 0 as n
    union all
    select n   1
    from Numbers
    where n < 365
)
select DATEADD(day, n, @StartDate)
from Numbers
option(maxrecursion 0)

From here it's a simple matter to use the EndDate in a WHERE clause to limit the total rows:

DECLARE @StartDate date = '20221212'; 
DECLARE @EndDate date = '20221231';

with Numbers as (
    select 0 as n
    union all
    select n   1
    from Numbers
    where n < DATEDIFF(day, @StartDate, @EndDate)
)
select DATEADD(day, n, @StartDate)
from Numbers
option(maxrecursion 0)