Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Finding the Correct Weekday Regardless of DateFirst Expand / Collapse
Author
Message
Posted Friday, February 19, 2010 1:37 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, March 27, 2014 8:16 AM
Points: 534, 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
Post #868823
Posted Friday, February 19, 2010 2:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 13, 2014 4:28 AM
Points: 4, Visits: 35
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
Posted Friday, February 19, 2010 2:06 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, October 13, 2014 6:12 AM
Points: 841, Visits: 332
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.
Post #868836
Posted Friday, February 19, 2010 2:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, October 5, 2014 1:48 AM
Points: 143, Visits: 551
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
Posted Friday, February 19, 2010 2:19 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, October 5, 2014 1:48 AM
Points: 143, Visits: 551
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
Posted Friday, February 19, 2010 2:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 13, 2014 4:28 AM
Points: 4, Visits: 35
Hi Divya

Monday would be day 1, but a sunday returns 0.
Post #868847
Posted Friday, February 19, 2010 2:29 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, March 27, 2014 8:16 AM
Points: 534, 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
Post #868849
Posted Friday, February 19, 2010 2:34 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 2:24 AM
Points: 363, Visits: 1,324
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
Posted Friday, February 19, 2010 2:48 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 2,556, Visits: 2,417
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
Posted Friday, February 19, 2010 2:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase ««12345»»»

Permissions Expand / Collapse