Finding the Correct Weekday Regardless of DateFirst

  • Comments posted to this topic are about the item Finding the Correct Weekday Regardless of DateFirst


  • In many cases, I can do what I have to do by simply capturing the weekday values for a known saturday and sunday, like so:

    declare @dowSat int

    declare @dowSun int

    set @dowSat=datepart(dw,'20061028')

    set @dowSun=datepart(dw,'20061029')

  • Nice tip, Divya. I'm adding this to my bookmarks next to that for "Some Common Date Routines" that Lynn Pettis put up a while ago.


    Edit to correct link

  • Yes Michael, that is certainly there. My post gives an idea of forthcoming weekends without having any intimation of dates.


  • Thanks John.

    I am not able to open Lynn Pettis post..


  • Good Article Divya....

    This is Another way..

    set datefirst 2

    select datediff(dd,5,'12/17/2009')%7

    set datefirst 5

    select datediff(dd,5,'12/17/2009')%7

  • Excellent Article Divya.....


  • This is a great tip. Thanks.

    Can I raise a question regarding it though?

    Maybe I'm misreading but if the date you pick is Saturday (instead of Thursday in the example - 17th Dec 2009 was a Thursday I believe), unless I'm very much mistaken by the values returned from modulo, the final result after applying the modulo will be zero. Seven needs to be added in this case.

  • This code is indipendent from datefirst:

    Monday is 0 and Sunday is 6

    SELECT cast(cast('20100215' as datetime) as int)%7

    SELECT cast(cast('20100216' as datetime) as int)%7

    SELECT cast(cast('20100217' as datetime) as int)%7

    SELECT cast(cast('20100218' as datetime) as int)%7

    SELECT cast(cast('20100219' as datetime) as int)%7

    SELECT cast(cast('20100220' as datetime) as int)%7

    SELECT cast(cast('20100221' as datetime) as int)%7

  • SELECT 1 + DATEDIFF(DAY, 0, '20091217') % 7

    N 56°04'39.16"
    E 12°55'05.25"

  • This article refers to the "English" calendar.

    Of course, it should refer to the "American" calendar.

    In England, like in most of the rest of Europe, the week is deemed to start on Monday, not Sunday. Thursday is, therefore, the fourth day of the week, not the fifth.

    To modify the script to cater for this, instead of simply adding 7 to the result before the modulus operation, it would be better to add 14 and then SUBTRACT the "standard" index number of the first day of the week in your locale. (This should cater for the zero problem pointed out in an earlier post.)

    (looks a lot more complex expressed generally than specifically)

    Similarly, the date format is different.

    Today in America is 02/19/2010, in England it's 19/02/2010.

    As a computer geek I actually prefer the Japanese method of writing dates (like 2010/02/19), as this can very easily be used as a (part of a) file name, eg SQLBackup_20100219.BAK - Now if you have many files in a single directory/folder you can sort by name and they're nicely in date order too 🙂

    BTW - I think that it is a very good article, just nit-picking American/British differences. After all, the Brits walk on the pavement, the Americans drive on it... 🙂

    Kelsey Thornton

  • Thanks for putting me in the right direction.

    If you want Monday to be day 1 and Sunday to be day 7 use this:

    SELECT ((DATEPART(dw, '2010-02-23') + @@DATEFIRST -2) % 7)+1 AS dw

    There's no day 0.

  • Nice and well written article. Just however

    the week as per the English calendar as to be starting from Sunday and ending on Saturday.

    I have a little problem with as per the English calendar the week starts on Monday and ends on Sunday. 😛

    Clearly the English are in a minority over the Americans but still there are two interpretations for the English calendar. :hehe:

  • Thanks Kelsey.

    Yes i might be mistaken, the format is American not English.

    Btw, even i like that Japanese format.

    Well, its quite easy to modify the script to get the weekday according to the format.

    If Monday is the first day of the week in English format. The original setting of datefirst will be 1

    Try selecting


    SELECT DATEPART(dw, '2009-12-17')

    -- 4 (If @@Datefirst is 1)

    To make it universal, just require a small change in the code

    SELECT (DATEPART(dw, '2009-12-17') + @@DATEFIRST-1) % 7 AS dw

    Now, try changing the datefirst setting and you will always get 4 for every DATEFIRST setting by running the above code.


  • Yes Patrick. You can even do like

    SELECT (DATEPART(dw, '2009-12-17') + @@DATEFIRST-1) % 7 AS dw

    For Monday to be the first day..:)

    Thanks Knut. I have cleared myself later on regarding the format 🙂


Viewing 15 posts - 1 through 15 (of 60 total)

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