How can I get, and re-use, the next 6 *dates* from today in SQL Server?

Question

I need to create a CTE I can re-use that will hold seven dates. That is today and the next six days.

So, output for today (4/22/2022) should be:

2022-04-22
2022-04-23
2022-04-24
2022-04-25
2022-04-26
2022-04-27
2022-04-28

So far, I have this:

WITH seq AS
(
    SELECT 0 AS [idx]
    UNION ALL 
    SELECT [idx] + 1
    FROM seq
    WHERE [idx] < 6
)
SELECT DATEADD(dd, [idx], CONVERT(date, GETDATE()))
FROM seq;

The problem is my SELECT is outside the WITH, so I would need to wrap this whole thing with another WITH to re-use it, for example to JOIN on it as a list of dates, and I'm not having luck getting that nested WITH to work. How else could I accomplish this?

To be clear: I'm not trying to find records in a specific table full of dates that are from the next seven days. There are plenty of easy solutions for that. I need a list of dates for today and the next six days, that I can re-use in other queries as a CTE.

Answer 1

You're close. Here's an example:

with cte as (
  select 
    1 as n
    ,GETDATE() as dt
  union all
  select 
    n+1
    ,DATEADD(dd,n,GETDATE()) as dt
  from cte
  where n <= 6
)
select * from cte

Fiddle here

You can create a view for reusability and simply query the view rather than using the same CTE over and over again.

Answer 2

You can do this by adding a second column for the date to the CTE:

WITH seq AS (
    SELECT 0 AS [idx], cast(current_timestamp as date) as date
    UNION ALL 
    SELECT [idx] + 1,  dateadd(dd, idx+1, cast(current_timestamp as date))
    FROM seq
    WHERE [idx] < 6
)
SELECT *
FROM seq;

See it here:

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=208ecd76be2071529078f38b1735b0cd

Another option is you can "stack" CTEs, rather than nest, to avoid the second column:

WITH seq0 AS (
    SELECT 0 AS [idx]
    UNION ALL 
    SELECT [idx] + 1
    FROM seq0
    WHERE [idx] < 6
),
seq As (
    SELECT dateadd(dd, idx, cast(current_timestamp as date)) as idx
    FROM seq0
)

SELECT *
FROM seq;

Note how the final query only needed to reference the 2nd CTE.

See it here:

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=22315438e4710792f368009cc6ff6451

Answer 3

Never recommend using recursion if you don't have a need for it. It's more complex and slower. I'd just use a hardcoded list of numbers, could encapsulate it in TVF if you wanted to reuse it across different stored procedures/functions. If you need to reuse it in 1 stored proc in multiple places, I'd just throw it in a temp table.

CTE Version without Recursion

WITH cte_7days AS (
    SELECT theDate = CAST(DATEADD(dd,num,GETDATE()) AS DATE)
    FROM (VALUES (0),(1),(2),(3),(4),(5),(6)) AS A(num)
)

SELECT *
FROM cte_7days

CROSS APPLY Version to Remove Need for CTE

Could use something like this as your base query, and then just add more joins below table depending on your query

SELECT theDate
FROM (VALUES (0),(1),(2),(3),(4),(5),(6)) AS A(num)
CROSS APPLY (SELECT theDate = CAST(DATEADD(DAY,num,GETDATE()) AS DATE)) AS B

TVF Version

CREATE FUNCTION dbo.uf_7days()
RETURNS TABLE AS
RETURN
(
    SELECT theDate
    FROM (VALUES (0),(1),(2),(3),(4),(5),(6)) AS A(num)
    CROSS APPLY (SELECT theDate = CAST(DATEADD(DAY,num,GETDATE()) AS DATE)) AS B
)
GO