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.
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)?
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 datetimedeclare @dtmTest2 datetimeset @dtmTest = 7set @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
