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

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:

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.

CodePudding user response：

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.

CodePudding user response：

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