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  gt   DATEADD day -7  GETDATE      I have two question    Is this query is correct  Is this is the fastest way to get the last seven day data from a table having millions of rows

User · Answer

to select records for the last 7 days  WHERE Created Date  gt   DATEADD day  -7  GETDATE      to select records for the current week   SET DATEFIRST 1 -- Define beginning of week as Monday SELECT   FROM WHERE CreatedDate  gt   DATEADD day  1 - DATEPART dw  GETDATE     CONVERT DATE  GETDATE        AND CreatedDate  lt   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  gt   DATEADD day  - DATEPART dw  GETDATE      6   CONVERT DATE  GETDATE        AND CreatedDate  lt   DATEADD day  1 - DATEPART dw  GETDATE     CONVERT DATE  GETDATE

User · Answer

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  gt   DATEADD day -11117  GETDATE

User · Answer

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

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

Examples related to sql-server-2008

Examples related to dateadd