DATEPART and DATENAME defaults don't work as documented - by rgarrison

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


1
0
Sign in
to vote
ID 795581 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 7/31/2013 12:45:20 PM
Access Restriction Public

Description

-- Reference: http://msdn.microsoft.com/en-us/library/ms174420.aspx
-- Section: Default Returned for a datepart That Is Not in a date Argument
-- "If the data type of the date argument does not have the specified datepart, the default for that datepart will be returned."

-- The literals in the example work, but variables and tables records fail.
-- I read elsewhere that the literal is converted to a datetime for backward compatibility.

-- Works
SELECT DATEPART(year, '12:10:30.123')
-- Fails
DECLARE @t time = '12:10:30.123';
SELECT DATEPART(year, @t);
-- Works
DECLARE @dt datetime = '12:10:30.123';
SELECT DATEPART(year, @dt);
-- Error:
-- Msg 9810, Level 16, State 3, Line 3
-- The datepart year is not supported by date function datepart for data type time.

-- Works
SELECT DATEPART(hour, '2007-06-01');
-- Fails
DECLARE @d date = '2007-06-01';
SELECT DATEPART(hour, @d);
-- Error:
-- Msg 9810, Level 16, State 2, Line 3
-- The datepart hour is not supported by date function datepart for data type date.
Sign in to post a comment.
Posted by rgarrison on 8/12/2013 at 3:55 PM
Thank you for the quick turnaround.

Rob
Posted by Microsoft on 8/12/2013 at 2:55 PM
The DATEPART and DATENAME topics have been updated to reflect the behavior you have provided. The topics will be published on Tuesday, August 13 (or shortly thereafter.

Thank you for reporting this discrepancy in our documentation.

Best,
Gail Erickson
SQL Server Documentation Team
Posted by Microsoft on 8/1/2013 at 11:01 AM
Thank you for reporting this discrepancy. We will investigate and update the content as appropriate as soon as possible.

Kind regards,
Gail Erickson
SQL Server Documentation Team