Finding the Correct Weekday Regardless of DateFirst

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Divya Agrawal » Finding the Correct Weekday Regardless of...

61 posts, Page 2 of 7

««1 2 3 4 5 »»»

Finding the Correct Weekday Regardless of DateFirst

Rate Topic

Display Mode

Topic Options

Author

Message

Kelsey Thornton

Posted Friday, February 19, 2010 1:37 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, March 04, 2013 4:10 AM
Points: 532, Visits: 281

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 Smile

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... Smile

Kelsey Thornton
MBCS CITP

Post #868823

Patrick Dekkers

Posted Friday, February 19, 2010 2:02 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, April 08, 2013 9:32 AM
Points: 4, Visits: 29

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.

Post #868833

Knut Boehnert

Posted Friday, February 19, 2010 2:06 AM

Right there with Babe

Group: General Forum Members
Last Login: Thursday, April 18, 2013 5:10 AM
Points: 766, Visits: 274

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. Tongue

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

Post #868836

Divya Agrawal

Posted Friday, February 19, 2010 2:15 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 4:59 AM
Points: 139, Visits: 469

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

Post #868840

Divya Agrawal

Posted Friday, February 19, 2010 2:19 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 4:59 AM
Points: 139, Visits: 469

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

Post #868844

Patrick Dekkers

Posted Friday, February 19, 2010 2:22 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, April 08, 2013 9:32 AM
Points: 4, Visits: 29

Hi Divya

Monday would be day 1, but a sunday returns 0.

Post #868847

Kelsey Thornton

Posted Friday, February 19, 2010 2:29 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, March 04, 2013 4:10 AM
Points: 532, Visits: 281

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 Wink

Kelsey Thornton
MBCS CITP

Post #868849

dmoldovan

Posted Friday, February 19, 2010 2:34 AM

Old Hand

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 5:51 AM
Points: 360, Visits: 1,260

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

Post #868853

Carlo Romagnano

Posted Friday, February 19, 2010 2:48 AM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 2:04 AM
Points: 1,968, Visits: 1,819

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.

Post #868863

khayot.khalimov

Posted Friday, February 19, 2010 2:55 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, November 26, 2010 7:47 AM
Points: 5, 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

Post #868871

« Prev Topic | Next Topic »

61 posts, Page 2 of 7

««1 2 3 4 5 »»»

Permissions