Raw SELECT (without FROM) of most recent 7 days to current-CodePudding

I want to get the query result (e.g. to populate table) of last 7 dates (without times). I know that we can select some scalars without FROM statement. So I ended up with following solution:

select DATEADD (DAY, 0, CONVERT(date, GetDate()))
Union
select DATEADD (DAY,-1, CONVERT(date, GetDate()))
Union
select DATEADD (DAY,-2, CONVERT(date, GetDate()))
Union
select DATEADD (DAY,-3, CONVERT(date, GetDate()))
Union
select DATEADD (DAY,-4, CONVERT(date, GetDate()))
Union
select DATEADD (DAY,-5, CONVERT(date, GetDate()))
Union
select DATEADD (DAY,-6, CONVERT(date, GetDate()))

Please point me to better (and more elegant) solution if there is one.

CodePudding user response：

The VALUES, table value constructor, is a little more concise.

select dateadd(day, x.num, convert(date, getdate()))
from (
  values (0), (-1), (-2), (-3), (-4), (-5), (-6)
) x (num)
order by x.num;

Returns:

Date
2021-11-18
2021-11-19
2021-11-20
2021-11-21
2021-11-22
2021-11-23
2021-11-24

CodePudding user response：

You can also expand on the values table by using either a permanent numbers/tally table (always handy) or by generating an artibrary list of numbers from any suitable table, you can then create a list of dates for any period

select dateadd(day, n.v, convert(date, getdate()))
from (
  select top (30) v=-1 * Row_Number() over(order by (select 1))
    from master.dbo.spt_values
)n
order by n.v;