Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

datename-datepart weirdness Expand / Collapse
Author
Message
Posted Wednesday, March 24, 2004 7:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 07, 2007 6:59 AM
Points: 38, 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.

Post #107947
Posted Wednesday, March 24, 2004 7:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 1:18 AM
Points: 5,956, Visits: 285
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/
Post #107950
Posted Wednesday, March 24, 2004 7:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 07, 2007 6:59 AM
Points: 38, 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)?

Post #107954
Posted Wednesday, March 24, 2004 9:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 10:24 AM
Points: 155, Visits: 234

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




Post #107985
Posted Wednesday, March 24, 2004 11:51 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 07, 2007 6:59 AM
Points: 38, Visits: 1
I think I get it now. Thanks.
Post #108029
Posted Thursday, March 25, 2004 12:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 1:18 AM
Points: 5,956, Visits: 285
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/
Post #108142
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse