Finding the Correct Weekday Regardless of DateFirst

Question

Finding the Correct Weekday Regardless of DateFirst

Divya Agrawal

Hall of Fame

Points: 3144
More actions
February 18, 2010 at 10:12 pm

#219325

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

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

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

Michael Meierruth SSChampion Points: 10051 More actions · Answer 1

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')

john.arnott SSChampion Points: 11882 More actions · Answer 2

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

Divya Agrawal Hall of Fame Points: 3144 More actions · Answer 3

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

--Divya

Divya Agrawal Hall of Fame Points: 3144 More actions · Answer 4

Thanks John.

I am not able to open Lynn Pettis post..

--Divya

ramireddydasaradha SSC Journeyman Points: 99 More actions · Answer 5

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

Satnam Singh Hall of Fame Points: 3542 More actions · Answer 6

Satnam Singh

Hall of Fame

Points: 3542

February 19, 2010 at 12:17 am

#1121584

Excellent Article Divya.....

Satnam

davidgr144 SSC Enthusiast Points: 125 More actions · Answer 7

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.

Carlo Romagnano SSC-Insane Points: 22668 More actions · Answer 8

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

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 9

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

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

Kelsey Thornton SSCrazy Points: 2157 More actions · Answer 10

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
MBCS CITP

Patrick Dekkers SSC Enthusiast Points: 115 More actions · Answer 11

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.

Knut Boehnert SSCrazy Points: 2951 More actions · Answer 12

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:

Divya Agrawal Hall of Fame Points: 3144 More actions · Answer 13

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 @@DATEFIRST

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.

--Divya

Divya Agrawal Hall of Fame Points: 3144 More actions · Answer 14

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 🙂

--Divya