Home Forums SQL Server 2005 SQL Server 2005 General Discussion DATEFIRST setting oddity RE: DATEFIRST setting oddity

  • November 5, 2010 at 11:37 am

    #1247257

    I'm not sure if you're looking for a "static" function (in the meaning of returning the first day of a week regardless of the DATEFIRST setting)...

    In this case you could use

    SELECT DATEADD(wk,DATEDIFF(wk,0,YourDateValue),0)

    This will always return the date for Monday of the week in question. However, it will start changing to the next week on Sunday. I you want to change the day to start a new week you'd need to subtract the "offset" to Sunday. For example, if you want to have Sunday belonging to the previous week and start with Monday, you'd need to subtract 1.

    SELECT DATEADD(wk,DATEDIFF(wk,0,YourDateValue - 1),0) Or are you looking for a fast ISO week calculation?


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]