February 19, 2010 at 2:22 am
Hi Divya
Monday would be day 1, but a sunday returns 0.
February 19, 2010 at 2:29 am
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
February 19, 2010 at 2:34 am
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
February 19, 2010 at 2:48 am
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.
February 19, 2010 at 2:55 am
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'
selectcasewhen (datepart(weekday,@Date) + @@Datefirst - 1)%7 = 0
then 7
else (datepart(weekday,@Date) + @@Datefirst - 1)%7
end
February 19, 2010 at 3:03 am
Carlo Romagnano (2/19/2010)
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.
That is what I was saying in my last post.
Kelsey Thornton
MBCS CITP
February 19, 2010 at 3:04 am
Our goal is to get the day of the week for the date '2009-12-17' as '5' regardless of the DateFirst setting.
SET DATEFIRST lets you define, when you want your week to start.
DATEPART (dw, <date>) gets you the "day of the week" for <date>, based upon the DATEFIRST definition we just did.
NOW:
"Our goal is to get the day of the week for the date '2009-12-17' as '5' regardless of the DateFirst setting."
sounds quite ... strange:
2009-12-17 was a Thursday.
You start your week on Sunday or Monday or Friday: 2009-12-17 was a Thursday!
Now: If you start your week on Monday, Thursday is the 4th day of the week, not the 5th.
Why should you make it the 5th, when it is the 4th?
February 19, 2010 at 3:07 am
We have US and UK offices, and often encountered this problem with one user getting one result and another a different one for the same "DATEPART(dw" statement (as the DATEFIRST is user dependent based on language rather than overall on the server).
We have many developers and trying to get them all to adopt a new way of returning the day of the week would be impossible, and chances are you would forget once in a while, or a new member of staff would simply use the original code anyway.
Instead of that we encourage users to always explicitly specify SET DATEFIRST in their code, and we run a check each night for any procs that use "DATEPART(dw" without a DATEFIRST in them, with this:
EXEC master.dbo.sp_MSForEachDB 'USE ?
SELECT DB_NAME() AS "Database", xtype, name FROM
(SELECT DISTINCT so.id, so.name, xtype from syscomments sc WITH (NOLOCK) JOIN sysobjects so WITH (NOLOCK) on sc.id = so.id
WHERE text like ''%datepart%dw%''
AND not exists (SELECT * from syscomments s WITH (NOLOCK) where text like ''%DATEFIRST%'' AND s.id = sc.id) ) A
'
It's fairly basic, but it can help catch issues.
February 19, 2010 at 3:16 am
To get Monday=1 and Sunday=7 I've been using this for years...
Select ((DatePart(dw,'2009-12-17') + @@DateFirst -2)%7)+1
February 19, 2010 at 3:18 am
i like this solution "((DatePart(dw,'2009-12-17') + @@DateFirst -2)%7)+1"
this is the best
February 19, 2010 at 3:50 am
Kelsey Thornton (2/19/2010)
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.
<snip>
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 🙂
I agree with everything you say 😀
your "japanese" method of writing dates - thats actually the international standard
February 19, 2010 at 4:40 am
It disables @@DATEFIRST and it's only usable if Sunday is firstday. So not usable for me.
February 19, 2010 at 4:55 am
Jan v.d. Kruyk (2/19/2010)
It disables @@DATEFIRST and it's only usable if Sunday is firstday. So not usable for me.
You have to make a bit change in the script, as per the first day of your week. If you have monday as the first day then just change the code to
SELECT (DATEPART(dw, '2009-12-17') + @@DATEFIRST-1) % 7 AS dw
This has been conveyed in the discussion.
Let me know if you any further queries 🙂
--Divya
February 19, 2010 at 4:58 am
davidgr144 (2/19/2010)
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.
Yep, I just tried that with
SELECT (DATEPART(dw, '2010-02-20') + @@DATEFIRST) % 7 AS dw
and the result is 0, I have just updated my case statement to use 0 to 6 as the numbers.
Theoretically you could add 1 to the results, but then SAT would be 1 and Friday 7.
Simple code to convert to a 3 letter day of the week:
DECLARE @lc_DEBUG VARCHAR(5)
DECLARE @lc_todayIs CHAR(3)
SET @lc_DEBUG = 'TRUE'
SELECT @lc_todayIs =
CASE (DATEPART(dw, GETDATE()) + @@DATEFIRST) % 7 -- Thanks to SQL Server Central .com website.
WHEN 2 THEN 'MON'
WHEN 3 THEN 'TUE'
WHEN 4 THEN 'WED'
WHEN 5 THEN 'THU'
WHEN 6 THEN 'FRI'
WHEN 0 THEN 'SAT'
WHEN 1 THEN 'SUN'
ELSE 'ERR'
END
IF @lc_DEBUG = 'TRUE'
SELECT '@todayIs Set to: ' AS Info, @lc_todayIs AS Val
BEM - Fixed to use GETDATE()
BEM 2 - Fixed GETDATE() into the correct spot.
BEM 3 - Edited my example to a Saturday date. Argh
February 19, 2010 at 5:12 am
Good idea ,we are eager to learn from u .
Viewing 15 posts - 16 through 30 (of 60 total)
You must be logged in to reply to this topic. Login to reply