How to select last one week data from today's date

Question

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 ?

Answer 1

Yes, the syntax is accurate and it should be fine.

Here is the SQL Fiddle Demo I created for your particular case

create table sample2
(
    id int primary key,
    created_date date,
    data varchar(10)
  )

insert into sample2 values (1,'2012-01-01','testing');

And here is how to select the data

SELECT Created_Date
FROM sample2
WHERE Created_Date >= DATEADD(day,-11117, GETDATE())

Answer 2

to select records for the last 7 days

SELECT * FROM [TableName]
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 [TableName]
WHERE CreatedDate >= DATEADD(day, 1 - DATEPART(WEEKDAY, GETDATE()), CONVERT(DATE, GETDATE())) 
  AND CreatedDate <  DATEADD(day, 8 - DATEPART(WEEKDAY, 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 [TableName] 
WHERE CreatedDate >= DATEADD(day, -(DATEPART(WEEKDAY, GETDATE()) + 6), CONVERT(DATE, GETDATE())) 
  AND CreatedDate <  DATEADD(day, 1 - DATEPART(WEEKDAY, GETDATE()), CONVERT(DATE, GETDATE()))

Answer 3

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

Answer 4

To select records for the last 7 days and last week from today's date:

SELECT * FROM [TableName]
WHERE created_date >= now() - interval '1 week'