SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


datename-datepart weirdness


datename-datepart weirdness

Author
Message
ursus-129623
ursus-129623
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 1

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.


Frank Kalis
Frank Kalis
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20107 Visits: 289
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/
ursus-129623
ursus-129623
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 1

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
maraclec
Old Hand
Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)

Group: General Forum Members
Points: 329 Visits: 236

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
ursus-129623
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 1
I think I get it now. Thanks.
Frank Kalis
Frank Kalis
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20107 Visits: 289
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/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search