Sign up ×
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.


Operand data type date is invalid for add operator.


Operand data type datetime2 is invalid for add operator.


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.


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


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
I think will work, too: DATEADD(day, DATEDIFF(day,'19000101',@D), CAST(@T AS DATETIME2(7))) : Fiddle – ypercube Oct 13 '13 at 15:12
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

3 Answers 3

up vote 21 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
@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
@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

First, I do realize that I'm responding to a 2 year old post but it may help others that run across it.

I could certainly be incorrect in my perception of what the true underlying problem is that the OP has but I can think of one and only one reason why someone would want to add '23:59:59.9999999' to a "whole" (Date with no time or a midnight time) and that is to use BETWEEN to delineate temporal criteria such as in the following code example...

 SELECT *SomeColumnList*
   FROM dbo.SomeTable
  WHERE SomeDateTimeColumn BETWEEN @StartDate AND @EndDate

The problem there, of course, is if the SomeDateTimeColumn DOES have a time element to it and @EndDate is a "whole" date, then the rows returned will not include any rows where SomeDateTimeColumn contains the desired @EndDate if it has other then a midnight time. In plain English, the criteria will not include most of the end date and so people take to adding the last possible instant of a day as a time (such as '23:59:59.9999999') to the given "whole" date so that all of the rows for the date described by @EndDate are actually returned.

I rarely use the word "NEVER" but I'll go out on a limb and state that even if "whole" dates are "guaranteed" to be the case in a given column, you should NEVER use BETWEEN for temporal criteria because of two things.

  1. There are no savings in CPU time because BETWEEN resolves to a

    =/<=condition behind the scenes. This is known as a Closed/Closed temporal range definition because both end points are INCLUSIVE.

  2. Because of the Closed/Closed temporal nature of #1 above, you have to go through the gyrations of adding the most resolute time value you can to the "whole" end date to cover all contingencies to make your code work for any resolution and to make it bullet-proof in the face of a possible data-type change of the column in the future (including datatype changes made by MS).

Because I do strongly suspect that the real reason why the OP wants to add '23:59:59.9999999' to a "whole" date is to include all of a date using BETWEEN, let's simplify using a Closed/Open (Inclusive, First Exclusive) temporal criteria like the following for code that will easily survive any current date/time data-type, which also adds consistency to the method of defining temporal lookup ranges.

 SELECT *SomeColumnList*
   FROM dbo.SomeTable
  WHERE SomeDateTimeColumn >= @StartDate 
    AND SomeDateTimeColumn <  DATEADD(dd,1,@EndDate)

Another word that I rarely use is ALWAYS but the Closed/Open method above will ALWAYS work for any currently available date/time datatype for "whole" day lookups given that @StartDate and @EndDate have been initially assigned as a "whole" date.

share|improve this answer
Heh... -2 and no one with the hair to say why. – Jeff Moden Oct 30 at 0:53

Your Answer


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.