Getting individual dates from a date range using T-SQL

Question

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:

A calendar table would honestly be the best thing to use. If you don't have one, then invest the time to create one. — Thom A, Feb 02 '23 at 15:36
Does this answer your question? [Generate Dates between date ranges](https://stackoverflow.com/questions/7824831/generate-dates-between-date-ranges) — nbk, Feb 02 '23 at 15:37

score 0 · Answer 1 · answered Feb 02 '23 at 15:39

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

score 0 · Answer 2 · answered Feb 02 '23 at 15:42

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)

score 0 · Answer 3 · answered Feb 03 '23 at 14:23

For SQL Server 2022 and later you can use Generate_Series:

declare @StartDate as Date = '20221212', @EndDate as Date = '20221225';
select DateAdd( day, Value, @StartDate ) as TargetDate
  from Generate_Series( 0, DateDiff( day, @StartDate, @EndDate ) );

dbfiddle.

Getting individual dates from a date range using T-SQL

3 Answers3