Post reply

Start of Last Week

  • December 14, 2006 at 7:51 am

    #94837

    Hi,

    I'm using:  dateadd (DW,1-Datepart(dw,getdate()),getdate()))  to retrieve the "start of last week" and it brings back "Sunday 10/12/2006".

    Our business treats Mondays as the start of the week. What statement would give me Monday as the start of each week? Thanks, John

  • December 14, 2006 at 8:02 am

    #678083

    Try this:

    dateadd (DW,2-Datepart(dw,getdate()),getdate())

    Also you can use SET DATEFIRST 1 to set Monday as the first day of the week, and then your function would work.

  • December 14, 2006 at 8:12 am

    #678087

    Lynn is right, but you need the cuation, because DATEFIRST setting change is permanent. so make sure it would not affect anywhere in your database. 

    Use @@DATEFIRST to get the old setting and then SET DATEFIRST 1 and after your code put the old one.

    But the good option is that you should not change the server setting just make your code as lynn said subtracting 2.

    Bydefault the sunday is the start day of the week. If you are not sure that which is start day and this could be change by some one else then surely you need these function for same result all the time.

     

    cheers

  • December 14, 2006 at 8:16 am

    #678090

    Thanks Lynn, Ijaz.

  • December 14, 2006 at 10:21 am

    #678138

    I'm really sorry, but along the same lines, how would I ensure that the "END OF THIS WEEK" relates to the Sunday? Thanks

  • December 14, 2006 at 11:10 pm

    #678260

    DATEFIRST isn't server sensitive and is not a system wide setting when invoked from QA... it's session/spid sensitive...

    Run the following in one QA window...

    SET DATEFIRST 3

    SELECT @@DATEFIRST

    Run this in another QA window...

    SELECT @@DATEFIRST

    Anyway, doesn't matter, I suppose, because the following will do what you want no matter what DATEFIRST is set to...

     SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()-1)-1,0)   AS StartingMondayOfLastWeek,

            DATEADD(wk,DATEDIFF(wk,0,GETDATE()-1)  ,0)-1 AS EndingSundayOfLastWeek,

            DATEADD(wk,DATEDIFF(wk,0,GETDATE()-1)  ,0)   AS StartingMondayOfThisWeek,

            DATEADD(wk,DATEDIFF(wk,0,GETDATE()-1)+1,0)-1 AS EndingSundayOfThisWeek

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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