none
GetDate() - extracting last 7 days dataset RRS feed

  • Question

  • I have a date field within my dataset that looks like this...

    2008-01-08 00:00:00.000
    2009-09-16 00:00:00.000
    2010-12-20 00:00:00.000
    2005-07-22 00:00:00.000
    2010-05-19 00:00:00.000
    2010-11-04 00:00:00.000
    2010-08-11 00:00:00.000
    2006-08-16 00:00:00.000

    Within my WHERE clause can I use the GETDATE() feature to retrieve the last 7 days dataset.

    Wednesday, April 13, 2011 8:56 AM

Answers

  • Yes you can do like

    Where your_date_column between dateadd(day,-7,getdate()) and getdate()

    becuase your date time column is the midnight time so need -7

    • Proposed as answer by Vishal Gajjar Wednesday, April 13, 2011 9:52 AM
    • Marked as answer by Sam233 Wednesday, April 13, 2011 10:06 AM
    Wednesday, April 13, 2011 9:00 AM

All replies

  • Yes you can do like

    Where your_date_column between dateadd(day,-7,getdate()) and getdate()

    becuase your date time column is the midnight time so need -7

    • Proposed as answer by Vishal Gajjar Wednesday, April 13, 2011 9:52 AM
    • Marked as answer by Sam233 Wednesday, April 13, 2011 10:06 AM
    Wednesday, April 13, 2011 9:00 AM
  • hi,

    Yes you can using DATEADD():

    SELECT *
    FROM yourTable
    WHERE yourDateColumn > DATEADD(DAY, -7, GETDATE()) ;
    

    DATEADD():

    http://msdn.microsoft.com/en-us/library/ms186819.aspx

    Caveat: You need to handle the time portion when you using a DATETIME column, see

    http://www.karaszi.com/SQLServer/info_datetime.asp

    When using SQL Server 2008 it's quite simple:

    SELECT *
    FROM yourTable
    WHERE CAST(yourDateColumn AS DATE) > DATEADD(DAY, -7, CAST(GETDATE() AS DATE)) ;
    


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Wednesday, April 13, 2011 9:00 AM
  • SELECT * FROM tbl WHERE dt >=DATEADD(d,-7,GETDATE()) AND dt <DATEADD(d,1,GETDATE())
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, April 13, 2011 9:01 AM
    Answerer
  • Perhaps something like:

    where DateColumn >= dateadd(day, -7, getdate())

    OR

    where DateColumn >= cast(floor(cast(dateadd(day, -7, getdate()) as float)) as datetime)

     

    Second query will remove time for the condition.

     

    MC

     

    Wednesday, April 13, 2011 9:02 AM
  • This worked perfectly getting help from Steven Wang's post.

    where start_time between DATEADD(DAY,-7,GETDATE()) and CAST(GETDATE() AS DATE)

    Wednesday, October 29, 2014 6:08 PM