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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

* The overflow can be avoided in Azure SQL Database and SQL Server 2016, using DATEDIFF_BIG.


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
up vote 24 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

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
2  
The question is about adding a date to a time without losing precision. You are paying too much attention to the sample values used in the code. They have no specific meaning here and could by any value that demonstrates the loss of precision. – Mikael Eriksson Jan 16 at 17:19
    
The motivation for asking was actually just I needed a generic way to combine date and time for revision 2 of this answer – Martin Smith Feb 21 at 7:37
    
Thanks for the feedback, Martin. Apologies for the speculation on my part. – Jeff Moden Feb 21 at 22:02

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.