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:
This question is related to
sql-server-2008
dateadd
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()))
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
Source: Stackoverflow.com