[sql-server-2008] How to select last one week data from today's date

How to select week data (more precisely, last 7 days data) from the current date in the fastest way as I have millions or rows in the table. I have a time stamp of created_date in sql table.

I have tried this

SELECT Created_Date
FROM Table_Name
WHERE Created_Date >= DATEADD(day,-7, GETDATE())

I have two question:

  1. Is this query is correct?
  2. Is this is the fastest way to get the last seven day data from a table having millions of rows ?

This question is related to sql-server-2008 dateadd

The answer is


to select records for the last 7 days

WHERE Created_Date >= DATEADD(day, -7, GETDATE())

to select records for the current week

SET DATEFIRST 1 -- Define beginning of week as Monday
SELECT * FROM
WHERE CreatedDate >= DATEADD(day, 1 - DATEPART(dw, GETDATE()), CONVERT(DATE, GETDATE())) 
  AND CreatedDate <  DATEADD(day, 8 - DATEPART(dw, GETDATE()), CONVERT(DATE, GETDATE()))

if you want to select records for last week instead of the last 7 days

SET DATEFIRST 1 -- Define beginning of week as Monday
SELECT * FROM  
WHERE CreatedDate >= DATEADD(day, -(DATEPART(dw, GETDATE()) + 6), CONVERT(DATE, GETDATE())) 
  AND CreatedDate <  DATEADD(day, 1 - DATEPART(dw, GETDATE()), CONVERT(DATE, GETDATE()))

  1. The query is correct

2A. As far as last seven days have much less rows than whole table an index can help

2B. If you are interested only in Created_Date you can try using some group by and count, it should help with the result set size