How to select last seven day's dates in SQL Server

Question

I want to select last seven day's dates in the select statement as current date is 2018-07-12

Expected result :

Last_Seven_Days_Dates
2018-07-06
2018-07-07
2018-07-08
2018-07-09
2018-07-10
2018-07-11
2018-07-12

Possible duplicate of: https://stackoverflow.com/questions/27599557/how-to-get-last-7-days-data-from-current-datetime-to-last-7-days-in-sql-server — gcourtet, Jul 12 '18 at 13:08
Possible duplicate of [How to get last 7 days data from current datetime to last 7 days in sql server](https://stackoverflow.com/questions/27599557/how-to-get-last-7-days-data-from-current-datetime-to-last-7-days-in-sql-server) — dllhell, Jul 12 '18 at 13:09
I don't want to use that in any where condition just want a result as described in question and store it in temp table — Prashant Pimpale, Jul 12 '18 at 13:10

sniperd · Accepted Answer · 2018-07-12T13:27:49.350

If your expected results listed in the question are exactly what you are looking for, this will do the trick:

SELECT * FROM (
    SELECT CAST(GETDATE() AS DATE) AS Last_Seven_Days_Dates
    UNION
    SELECT CAST(DATEADD(DAY, -1, GETDATE()) AS DATE) AS Last_Seven_Days_Dates
    UNION
    SELECT CAST(DATEADD(DAY, -2, GETDATE()) AS DATE) AS Last_Seven_Days_Dates
    UNION
    SELECT CAST(DATEADD(DAY, -3, GETDATE()) AS DATE) AS Last_Seven_Days_Dates
    UNION
    SELECT CAST(DATEADD(DAY, -4, GETDATE()) AS DATE) AS Last_Seven_Days_Dates
    UNION
    SELECT CAST(DATEADD(DAY, -5, GETDATE()) AS DATE) AS Last_Seven_Days_Dates
    UNION
    SELECT CAST(DATEADD(DAY, -6, GETDATE()) AS DATE) AS Last_Seven_Days_Dates
    UNION
    SELECT CAST(DATEADD(DAY, -7, GETDATE()) AS DATE) AS Last_Seven_Days_Dates
) AS the_table

results:

Last_Seven_Days_Dates
---------------------
2018-07-05
2018-07-06
2018-07-07
2018-07-08
2018-07-09
2018-07-10
2018-07-11
2018-07-12

Since you stated you'll want these in a temp table, I've updated the answer to make it a subquery, a little easier to join or do something with.

Additionally if you need it in a temporary table you could do this:

SELECT * INTO #TempTableName FROM (
    SELECT CAST(GETDATE() AS DATE) AS Last_Seven_Days_Dates
    UNION
    SELECT CAST(DATEADD(DAY, -1, GETDATE()) AS DATE) AS Last_Seven_Days_Dates
    UNION
    SELECT CAST(DATEADD(DAY, -2, GETDATE()) AS DATE) AS Last_Seven_Days_Dates
    UNION
    SELECT CAST(DATEADD(DAY, -3, GETDATE()) AS DATE) AS Last_Seven_Days_Dates
    UNION
    SELECT CAST(DATEADD(DAY, -4, GETDATE()) AS DATE) AS Last_Seven_Days_Dates
    UNION
    SELECT CAST(DATEADD(DAY, -5, GETDATE()) AS DATE) AS Last_Seven_Days_Dates
    UNION
    SELECT CAST(DATEADD(DAY, -6, GETDATE()) AS DATE) AS Last_Seven_Days_Dates
    UNION
    SELECT CAST(DATEADD(DAY, -7, GETDATE()) AS DATE) AS Last_Seven_Days_Dates
)

thanks I also searched for above query. is there any option? — Prashant Pimpale, Jul 12 '18 at 13:14
sorry, I don't understand the question :) What option are you looking for? — sniperd, Jul 12 '18 at 13:15
Instead of doing union any other option to get last seven days dates? — Prashant Pimpale, Jul 12 '18 at 13:16
I suppose you could build a cursor or use a CTE but I think in the end you'll need to union the results. — sniperd, Jul 12 '18 at 13:19
I've updated the answer so it's a subquery now, so you could `JOIN` to it easily or put it into a temp table. — sniperd, Jul 12 '18 at 13:22
@sniperd I've added to an example of inserting to a temporary table to your answer and just changed the formatting. — Jason Geiger, Jul 12 '18 at 13:27

Tom Halson · Answer 2 · 2018-12-18T09:28:58.613

5

I use the following to generate date series

DECLARE @MinDate DATE = DATEADD(DAY, -7, GETDATE()),
        @MaxDate DATE = GETDATE();

SELECT  TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1)
        Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @MinDate)
FROM    sys.all_objects a;

You can wrap it in a function and pass in MinDate and MaxDate if you ever need to make it reusable

edited Dec 18 '18 at 09:28

answered Jul 12 '18 at 13:28

Tom Halson

380
3
12

score 3 · Answer 3 · answered Jul 12 '18 at 13:15

3

I would use a recursive CTE:

with dates as (
      select cast(getdate() as date) as dte, 1 as cnt
      union all
      select dateadd(day, 1, dte), cnt + 1
      from dates
      where cnt < 7
     )
select dte
from dates;

answered Jul 12 '18 at 13:15

Gordon Linoff

1,242,037
58
646
786

score 1 · Answer 4 · answered Jul 12 '18 at 13:07

1

you can use

DATEADD(DAY, -7, getDate()) < [date_field]

on your query

answered Jul 12 '18 at 13:07

Mohaalak

162
1
5

Zaynul Abadin Tuhin · Answer 5 · 2018-07-12T13:10:37.787

1

select * from your_table
where  date_field>=dateadd(day,-7,cast(getdate() as date)) and date_field<cast(getdate() as date)

edited Jul 12 '18 at 13:10

answered Jul 12 '18 at 13:09

Zaynul Abadin Tuhin

31,407
5
33
63

I don't want to use that in any where condition just want a result as described in question and store it in temp table – Prashant Pimpale Jul 12 '18 at 13:10

How to select last seven day's dates in SQL Server

5 Answers5

Linked