SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Finding the Correct Weekday Regardless of DateFirst


Finding the Correct Weekday Regardless of DateFirst

Author
Message
Kelsey Thornton
Kelsey Thornton
Say Hey Kid
Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)

Group: General Forum Members
Points: 711 Visits: 282
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
Patrick Dekkers
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 44
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
Knut Boehnert
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1203 Visits: 380
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. :-P

Clearly the English are in a minority over the Americans but still there are two interpretations for the English calendar. Hehe
Divya Agrawal
Divya Agrawal
Mr or Mrs. 500
Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)

Group: General Forum Members
Points: 586 Visits: 604
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
Divya Agrawal
Mr or Mrs. 500
Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)

Group: General Forum Members
Points: 586 Visits: 604
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..Smile

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

--Divya
Patrick Dekkers
Patrick Dekkers
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 44
Hi Divya

Monday would be day 1, but a sunday returns 0.
Kelsey Thornton
Kelsey Thornton
Say Hey Kid
Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)

Group: General Forum Members
Points: 711 Visits: 282
Just been re-thinking about this.

Maybe the original premise is that you always want 5 for a Thursday, not because *someone's* calendar says this, but because *the programming language's* calendar does?

Sunday is zero, so there should be no problem with that...

I was probably getting on my hobby-horse about British/American too soon ;-)

Kelsey Thornton
MBCS CITP
dmoldovan
dmoldovan
SSC Eights!
SSC Eights! (844 reputation)SSC Eights! (844 reputation)SSC Eights! (844 reputation)SSC Eights! (844 reputation)SSC Eights! (844 reputation)SSC Eights! (844 reputation)SSC Eights! (844 reputation)SSC Eights! (844 reputation)

Group: General Forum Members
Points: 844 Visits: 1469
Here is a nice tip from Itzik Ben Gan's "Inside Microsoft SQL Server 2005— T-SQL Programming" - January 1st 1900 as a Monday. So this will return all the records created on a Tuesday:
SELECT RecordID FROM Table
WHERE DATEDIFF(day, '19000102', RecordDate) % 7 = 0
Carlo Romagnano
Carlo Romagnano
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5826 Visits: 3340
The main problem is not the format of date (dmy or mdy or ymd) nor if monday is the first day of the week, but identify the day '2010-2-1' as monday.

DECLARE @d as datetime
set @d = '2010-2-1'
SELECT cast(@d as int) % 7 + 1

This identifies Monday as 1.

I run on tuttopodismo
khayot.khalimov
khayot.khalimov
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 68
this is what i've come up with years ago:
it returns Monday as 1, Teusday as 2 etc..., regardles of @@Datefirst.

declare @Date datetime
select @Date = '2009-12-17'

select case when (datepart(weekday,@Date) + @@Datefirst - 1)%7 = 0
then 7
else (datepart(weekday,@Date) + @@Datefirst - 1)%7
end
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search