Home Dashboard Directory Help
Search

SQL Server does not provide a way to combine date and time data types into one by Vladimir Moldovanenko


Status: 

Closed
 as Won't Fix Help for as Won't Fix


9
0
Sign in
to vote
Type: Bug
ID: 782056
Opened: 3/25/2013 10:53:10 AM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description


SQL Server 2008 provided date and time data types as separate types derived from datetime2 type. However, it didn't provide a way to combine these without string manipulation. datetime allowed + operator, datetime2 does not, for valid reasons. SQL 2012 added DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds ) and DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision )
functions. The first one does not support nanoseconds so data loss will occur. the second one is useful but time value cannot be split into needed parts as DATEPART(datepart, date) does not support (fractions, hour_offset, minute_offset, precision) options to extract these from time value.
Therefore, there is no native way to put date and time together into datetime2 with no data loss.

This was logged but refused, based on incomplete reasoning.
https://connect.microsoft.com/SQLServer/feedback/details/685251/implement-a-stuffdatetime-date-time-function#details

In addition, such trivial operation should not require such ugly solution to call and execute 10 function calls to get 10 parts of full datetime2 and then 1 call to put it together or having to use "to string" conversion/concatenation utilizing non-standard CONVERT.

Therefore, this is an oversight not to provide such solution. I understand that + operator is not a good way to do that.
I have read this discussion at http://social.technet.microsoft.com/Forums/en-US/transactsql/thread/700be335-d4e1-464b-97ff-ecb58985b026

user discussion link
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/63bc8609-5209-4d95-b4da-41708a1602b0/
Details
Sign in to post a comment.
Posted by Martin Smith on 10/14/2013 at 3:47 AM
There is a less ugly method than string manipulation supplied here http://dba.stackexchange.com/a/51443/3690

SELECT DATEADD(day, DATEDIFF(day,0,@D), CAST(@T AS DATETIME2(7)))

But I still support this request.

Having a DATETIMEFROMPARTS type function that accepted a Date and a Time would be much more intuitive.

Also I presume this would be more efficient than the date arithmetic approach as at the binary level a datetime2 just seems to be a date concatenated to a time2.
Posted by Microsoft on 4/12/2013 at 2:17 PM
Hello,
Thanks for your feedback. We have no plans to implement your feature request in a future version of SQL Server. So I am closing this request as "won't fix".
Please use the string conversion into a language neutral format like below:

DECLARE @dt date = SYSDATETIME(), @tm time = SYSDATETIME()
SELECT cast(convert(varchar(10), @dt, 126) + 'T' + convert(varchar(10), @tm, 114) as datetime)


--
Umachandar, SQL Programmability Team
Sign in to post a workaround.