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 |