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

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 782056 Comments
Status Closed Workarounds
Type Bug Repros 1
Opened 3/25/2013 10:53:10 AM
Access Restriction Public


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.

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
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


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
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