Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Given the following components

DECLARE @D DATE = '2013-10-13'
DECLARE @T TIME(7) = '23:59:59.9999999'

What is the best way of combining them to produce a DATETIME2(7) result with value '2013-10-13 23:59:59.9999999'?

Some things which don't work are listed below.


SELECT @D + @T 

Operand data type date is invalid for add operator.


SELECT CAST(@D AS DATETIME2(7)) + @T 

Operand data type datetime2 is invalid for add operator.


SELECT DATEADD(NANOSECOND,DATEDIFF(NANOSECOND,CAST('00:00:00.0000000' AS TIME),@T),@D)

The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.


SELECT CAST(@D AS DATETIME) + @T 

The data types datetime and time are incompatible in the add operator.


SELECT CAST(@D AS DATETIME) + CAST(@T AS DATETIME)

Returns a result but loses precision 2013-10-13 23:59:59.997

share|improve this question
    
I assume you're asking for a 2008+ solution, and not one that involves converting to a string and back? SELECT CONVERT(datetime2, CAST(@D AS varchar(MAX)) + ' ' + CAST(@T AS varchar(MAX))) is vomit-worthy, but it does work. –  Jon Seigel Oct 13 '13 at 14:34
    
@JonSeigel - Converting to string and back would be a perfectly valid answer. That occurred to me too but I was sure I must be missing some less vomit inducing method! I haven't tagged any specific version so if there is a nicer way in 2012/2014 it would be good to see it. –  Martin Smith Oct 13 '13 at 14:40
    
DATETIME2FROMPARTS is probably faster than doing the string conversion, but it's 2012+. Still kind of ugly, but not as hackish. –  Jon Seigel Oct 13 '13 at 14:56
3  
I think will work, too: DATEADD(day, DATEDIFF(day,'19000101',@D), CAST(@T AS DATETIME2(7))) : Fiddle –  ypercube Oct 13 '13 at 15:12
2  
There is a connect item that when implemented will allow the datediff on nanosecond variant. Add datediff_big –  Mikael Eriksson Oct 13 '13 at 17:32

2 Answers 2

up vote 10 down vote accepted

This seems to work and keep the precision as well:

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

The CAST to DATETIME2(7) converts the TIME(7) value (@T) to a DATETIME2 where the date part is '1900-01-01', which is the default value of date and datetime types (see datetime2 and the comment* at CAST and CONVERT page at MSDN.)

* ... When character data that represents only date or only time components is cast to the datetime or smalldatetime data types, the unspecified time component is set to 00:00:00.000, and the unspecified date component is set to 1900-01-01.

The DATEADD() and DATEDIFF()function take care of the rest, i.e. adding the difference in days between the 1900-01-01 and the DATE value (@D).

Test at: SQL-Fiddle

share|improve this answer
    
There are definitely some uglier alternatives, but I think @ypercube's is about as concise as you're going to get. I played with this until I realized that you can't get datediff in nanoseconds from midnight (overflow), making it much more complex than what I envisioned when I started: SELECT DATEADD(DAY, DATEDIFF(DAY,0,@D), CONVERT(DATETIME2(7),DATEADD(NANOSECOND, DATEDIFF(NANOSECOND,CONVERT(TIME(0),@T),@T), CONVERT(TIME(7),CONVERT(TIME(0),@T))))); –  Aaron Bertrand Oct 13 '13 at 16:09
2  
@AaronBertrand - I'm quite surprised that there is nothing built in to add date + time. I can see the argument here for disallowing it for time+time or date+date or datetime2+datetime2 but this seems something of an omission. –  Martin Smith Oct 13 '13 at 16:15
3  
@MartinSmith yeah, same here. Now, we were behind closed doors when Yukon was in beta, and we stopped them from shipping date and time in SQL Server 2005. The reason: they were even less complete than what they shipped in 2008. –  Aaron Bertrand Oct 13 '13 at 16:26
    
@AaronBertrand - I considered adding a connect item request for a function to combine date and time but I see there already is one closed as "won't fix" –  Martin Smith Oct 14 '13 at 10:41
    
Yeah it's too bad, when they develop a language feature, they pretty much ship it and then leave it alone. Look at all the unresolved bugs with MERGE that have been present since 2008 and still exist in 2014. Not enough noise about them, and fixing them won't sell licenses, so... –  Aaron Bertrand Oct 14 '13 at 12:11

It's pretty stupid of SQL Server not to let your first example work, and this is going to seem really dumb too, but…

select convert(datetime2, convert(nvarchar(max), @d) + ' ' + convert(nvarchar(max), @t));
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.