CODEWITHSUNDEEP
sqlsql-server
Prashant Pimpale
Prashant Pimpale

Reputation: 10717

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

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

Upvotes: 1

Views: 4161

Answers (5)

Tom Halson
Tom Halson

Reputation: 390

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

Upvotes: 5

sniperd
sniperd

Reputation: 5274

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
)

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1271151

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;

Upvotes: 3

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32021

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

Upvotes: 1

Mohaalak
Mohaalak

Reputation: 162

you can use 

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

on your query

Upvotes: 1

Related Questions