Home Dashboard Directory Help
Search

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


Status: 

Active


1
0
Sign in
to vote
Type: Bug
ID: 795581
Opened: 7/31/2013 12:45:20 PM
Access Restriction: Public
1
Workaround(s)
view
0
User(s) can reproduce this bug

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.
Details
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
Sign in to post a workaround.
Posted by rgarrison on 7/31/2013 at 12:55 PM
DECLARE @t time(3) = '12:10:30.123';
-- Instead of this which fails ...
SELECT DATEPART(year, @t);
-- Add a CONVERT()
SELECT DATEPART(year, CONVERT(datetime2(3), @t));

DECLARE @d date = '2007-06-01';
-- Instead of this which fails ...
SELECT DATEPART(hour, @d);
-- Add a CONVERT()
SELECT DATEPART(hour, CONVERT(datetime2(3), @d));