February 19, 2010 at 5:58 am
SELECT DATENAME(weekday, 'CCYY-MM-DD') does it for me. I would hope that this builtin function handles different DATEFIRST settings correctly.
February 19, 2010 at 6:17 am
Brian Munier (2/19/2010)
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
May i know ur datefirst setting?
Just try running "SELECT @@DATEFIRST"
This will give which day is considered as the first day of the week..
And according to that the script has to be changed..
I have considered @@DATEFIRST =7 i.e. Sunday as the first day of the week. So based on that for Thursday it is returning 5.
--Divya
February 19, 2010 at 6:20 am
svansickle (2/19/2010)
SELECT DATENAME(weekday, 'CCYY-MM-DD') does it for me. I would hope that this builtin function handles different DATEFIRST settings correctly.
Don't forget language issues. Where I am, when I do
SELECT DATENAME(weekday, GETDATE())
I get
venerdi
And, oh yes, '2010-02-19' will not work. You need to code '19-02-2010'. Now, that's a strange one.
February 19, 2010 at 6:22 am
SwePeso's solution works for me. It is independent from language and @@datefirst.
Good Point Peter!:-P
Have a nice day,Christoph
February 19, 2010 at 6:42 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.DECLARE @d as datetime
set @d = '2010-2-1'
SELECT cast(@d as int) % 7 + 1
This identifies Monday as 1.
DECLARE @d as datetime
set @d = '2010-2-1 19:00'
SELECT cast(@d as int) % 7 + 1
It reports 2 for me, due to integer translation.
N 56°04'39.16"
E 12°55'05.25"
February 19, 2010 at 6:48 am
The code as I submitted it works correctly, including the date string literal. The format for the date string literal argument to the function is ISO 8601 ANSI SQL ('CCYY-MM-DD'), this is independent of your date format system settings. You can read about it here: http://msdn.microsoft.com/en-us/library/ms180878.aspx
I misunderstood the intention of the article and thought the intention was to get a reliable day name string, not a day number. The example that I gave was intended to give a translated day name; that's why I suggested this way of doing it.
Scott V
February 19, 2010 at 6:59 am
Hi Scott,
(I have corrected my first posting here:) yes you are right with your code, because DATENAME expects a date. But in the other cases where dateadd is used the style 'CCYY-MM-DD' is not working. ISO 8601 means a complete date + time for datetime, e. g.
2004-05-23T14:25:10
See: http://msdn.microsoft.com/en-us/library/ms180878.aspx#ISO8601Format
So the often used format 2004-05-23 14:25:10 (without the T in the middle) is the unstructured SQL Server format. This is not language independent, whereas 20040523 14:25:10 would work in germany, too.
set language german
DECLARE @d as datetime
set @d = '2004-05-23 14:25:10 '
select @d
go
DECLARE @d as datetime
set @d = '20040523 14:25:10 '
select @d
'2004-05-23' works only with date, because ISO 8601 defines this format for a date without a time-part.
Have a nice day,Christoph
February 19, 2010 at 7:23 am
You are correct that a string literal date with separators is not multi-language compatible, but it is DATEFORMAT independent. My example should be revised to use unseparated 'CCYYMMDD' format, which is listed by the MSDN link as a valid ISO 8601 format that is multilanguage compatible and DATEFORMAT independent. I would prefer not specifying a time portion because we are just looking for a day name, but I guess that adding some kind of valid time string as you described would be fine. It is unclear to me based on the MSDN article whether the ISO unseparated numeric date format is an international standard. If so, I would prefer going with that when the time is irrevelant.
Scott V
February 19, 2010 at 9:06 am
SwePeso (2/19/2010)
SELECT 1 + DATEDIFF(DAY, 0, '20091217') % 7
This works because 1 January 1900 was a Monday.
I knew that tidbit (and used it in a comment on Lynn's article on common datetimes), so should have recognized it could be a more diirect way to calcuate a numeric day-of-week. Simpler code to look at and presumably more efficient than involving @@DATEFIRST.
"Some Common Date Routines" by Lynn Pettis.
February 19, 2010 at 9:25 am
Divya Agrawal (2/19/2010)
May i know ur datefirst setting?
Just try running "SELECT @@DATEFIRST"
This will give which day is considered as the first day of the week..
And according to that the script has to be changed..
I have considered @@DATEFIRST =7 i.e. Sunday as the first day of the week. So based on that for Thursday it is returning 5.
Yes my datefirst was set to 7, but according to the article the trick removed the dependence on datefirst setting.
honestly, I had been looking for this function off and on, but now I think I will use the far more simpler form of:
DECLARE @lc_DEBUG VARCHAR(5)
DECLARE @lc_todayIs CHAR(3)
SET @lc_DEBUG = 'TRUE'
-- Even simpler way presuming we stay in English -- Thanks to SQL Server Central .com website.
SELECT @lc_todayIs =UPPER(SUBSTRING(DATENAME(weekday, GETDATE()), 1,3))
IF @lc_DEBUG = 'TRUE'
SELECT '@todayIs Set to: ' AS Info, @lc_todayIs AS Val
Since we only deal with english as the default language. The above gives me the three letter abbreviations of the day of the week which lets me query by what day it is.
February 19, 2010 at 11:12 am
Nice Article.
Thanks
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 19, 2010 at 11:37 am
Another solution is to use date math.
DECLARE @dt DATETIME,
@Day_Start SMALLINT
SET @Day_Start = 0--0=Sun,1=Mon,2=Tue,3=Wed,4=Thu,5=Fri,6=Sat
SET @dt = '20091219'
SELECT
--Day Of Week formula:
--datediff of @Day_Start (this is the start day, for the week of @dt) and @dt + 1 day.
DATEDIFF(
DAY,
CASE
WHEN @dt >= DATEADD(DAY,@Day_Start,DATEADD(WEEK,DATEDIFF(WEEK,0,@dt),0))
THEN DATEADD(DAY,@Day_Start,DATEADD(WEEK,DATEDIFF(WEEK,0,@dt),0)) --Get the start of this week & add @Day_Start
ELSE DATEADD(DAY,@Day_Start,DATEADD(WEEK,-1,DATEADD(WEEK,DATEDIFF(WEEK,0,@dt),0)))--Get the start of last week & add @Day_Start
END,
DATEADD(DAY,1,@dt) --Add a day to get the correct count
)
February 19, 2010 at 2:18 pm
It's interesting how in this discussion we have all been very busy trying to map numbers to the days of the week to 'make sense out of them' and to 'get a handle on them'. Whenever I have to deal with days of the week I make a concerted effort to stay away from that. And it's surprising how many problems you can solve that involve days of the week without ever involving the concept of Monday=1, Tuesday=2, etc.
February 20, 2010 at 4:25 am
I think Lynn's solution is best using
SELECT 1 + DATEDIFF(DAY, 0, @target_date) % 7
but it will fail for dates prior to 1/1/1900 - not a problem in most scenarios
but can be fixed with
SELECT 1 + (DATEDIFF(DAY, 0, @target_date)+700000) % 7
The 700000 can be any number divisible by 7. This will work all the way back to before the year 0 so should be fine for any non-biblical applications 🙂
I'd thought of:
SELECT (convert(int,convert(float,@target_date))+70000)%7+1
But the DATEDIFF solution is much more elegant and probably executes faster. CONVERTing direct to an INT would be make it much better but SQL likes to round UP if the datetime is past midday (pain).
However if you're only dealing with dates as at midnight then this works:
SELECT (convert(int,@target_date)+70000)%7+1
And if you're only dealing with midnight dates and you're not bothered about dealing with pre 1900 dates then this works:
SELECT convert(int,@target_date)%7+1
The shortest solution thus far methinks :w00t:
February 21, 2010 at 4:26 am
The method I posted earlier works fine for pre-1900 dates and gives an answer where mon=1 and sun=7
Select ((DatePart(dw,'2009-12-17') + @@DateFirst -2)%7)+1
Others have posted various ways of deriving the name of the day of week. I don't know why you wouldn't just do this...
Select DateName(dw,'2009-12-17')
If you only want the first 3 letters then...
Select Left(DateName(dw,'2009-12-17'),3)
Viewing 15 posts - 31 through 45 (of 60 total)
You must be logged in to reply to this topic. Login to reply