Post reply

Data for past one week

  • November 14, 2003 at 9:36 am

    #81552

    Does anyone have a sql which would select data for past one week and the date is defined as string type.

    TIA

  • November 14, 2003 at 9:59 am

    #482088

    Something like this...

    

    SELECT ...

    FROM ...

    WHERE DateDiff(Day, MyDateField, getdate()) <=7

    You might need some casting on the MyDateField. Post the exact format of the string if you can't work it out yourself.

  • November 14, 2003 at 2:08 pm

    #482136

    Thanks so much. How about getting the data for last wk ? TIA

  • November 14, 2003 at 2:36 pm

    #482138

    Sorry i meant to say what if i want to get data for week before the current week.

    Thanks

  • November 15, 2003 at 5:10 pm

    #482154

    SELECT ...

    FROM ...

    WHERE myCol >= DATEADD(dd, -7 + -DATEPART(dw,getdate()), CONVERT(char(8),getdate(),112))

    AND myCol < DATEADD(dd, 1 + -DATEPART(dw,getdate()), CONVERT(char(8),getdate(),112))

    This should capture the previous weeks end points dynamically, and also allows you to avoid using functions on the table columns, so you can index it.

    NOTE: Assumes your week is Sunday to Saturday

    Jeff

  • November 17, 2003 at 1:26 pm

    #482303

    Remember to look at your

    SET DATEFIRST and @@DATEFIRST. Do not assume

    Sunday - Saturday weeks in SQL.

    Also Try DateDiff(wk, ...

    instead of DateDiff(day, ...) ... 7



    Once you understand the BITs, all the pieces come together

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply