datename-datepart weirdness

Question

datename-datepart weirdness

ursus-129623

SSC Journeyman

Points: 78
More actions
March 24, 2004 at 7:10 am

#107804

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.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

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]

ursus-129623 SSC Journeyman Points: 78 More actions · Answer 2

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)?

maraclec Ten Centuries Points: 1175 More actions · Answer 3

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

ursus-129623 SSC Journeyman Points: 78 More actions · Answer 4

ursus-129623

SSC Journeyman

Points: 78

March 24, 2004 at 11:51 am

#500281

I think I get it now. Thanks.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

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]