Skip to content
Advertisement

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

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.

Advertisement

Answer

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
User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement