March 24, 2004 at 7:10 am
I'm currently playing with a query that needs to return all the appointments for a physician three business days ahead of their appointments. I think I can deal with the logic, but when I started playing with the "pretty-report-that-management-can-read" bit, I ran into an odd behavior when using datepart and datename.
I won't bore you with the whole thing, but if I enter the query:
select @@datefirst as 'Datefirst',datename(dw,@@datefirst) as 'WD Name'
,datename(dw,datepart(d,@@datefirst)) as 'DP WD Name'
this is the result I get:
Datefist WD Name DP WD Name
7 Monday Tuesday
If I read BOL correctly, the results should be 7 (default), Sunday, Sunday, right? Why the discrepancy? I'm sure it's a stupid oversight on my part, but I'd like your help in figuring this out before I start calling patients to remind them of their Sunday appointments.
March 24, 2004 at 7:40 am
Look at the explanation of DATENAME. As you are supplying @@datefirst as your date, SQL Server assumes that you are looking for the values of day 7 according to SQL Server date arithmetic, which is 08/01/1900
select
dateadd(dd,7,0) as 'Datefirst'
, datename(dw,7) as 'WD Name'
, datename(dw,datepart(d,7)) as 'DP WD Name'
Datefirst WD Name DP WD Name
------------------------------------------------------ ------------------------------ ------------------------------
1900-01-08 00:00:00.000 Montag Dienstag
(1 row(s) affected)
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
March 24, 2004 at 7:56 am
True enough, but that's not what I'm asking. (My fault, I should have been clearer).
The question is: why does datename(dw,datepart(d,7)) returns a value of one day more than datename(dw,7)?
March 24, 2004 at 9:27 am
The reason for the difference is this:
Datename and Datepart are require dates, in this case they are being given integers, which are being converted to dates.
7 is converted to 1900-01-08 00:00:00.000 (this happens to be a Monday), if you then take the datepart (day) of that its 8. 8 is converted to 1900-01-09 00:00:00.000, which happens to be a Tuesday.
Here is some code to show what I am saying.
declare @dtmTest datetime
declare @dtmTest2 datetime
set @dtmTest = 7
set @dtmTest2 = datepart(d, @dtmTest)
select @dtmTest, @dtmTest2, datename(dw, @dtmTest), datepart(d, @dtmTest), datename(dw, @dtmTest2)
You will get :
--------------------------- --------------------------- ------------------------------ ----------- ------------------------------
1900-01-08 00:00:00.000 1900-01-09 00:00:00.000 Monday 8 Tuesday
(1 row(s) affected)
I hope this helps,
Chuck
March 24, 2004 at 11:51 am
I think I get it now. Thanks.
March 25, 2004 at 12:19 am
Sorry, for being late on this (was waiting for a friend to explain this to me. You know who you are! )
The reason is fairly simple:
There are no 0st days in a month
SELECT CAST(0 AS datetime)
------------------------------------------------------
1900-01-01 00:00:00.000
(1 row(s) affected)
That's the first (day one, not zero). So DATEPART(d,7) = 8 and DATEPART(d,8) = 9...
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy