48

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

49

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


As noticed by @Quandary, the above expression is considered not-determninistic by SQL Server. If we want a deterministic expression, say because it is to be used for a PERSISTED column, the '19000101'** needs to be replaced by 0 or CONVERT(DATE, '19000101', 112):

CREATE TABLE date_time
( d DATE NOT NULL,
  t TIME(7) NOT NULL,
  dt AS DATEADD(day, 
                DATEDIFF(day, CONVERT(DATE, '19000101', 112), d), 
                CAST(t AS DATETIME2(7))
               ) PERSISTED
) ;

**: DATEDIFF(day, '19000101', d) isn't deterministic as it does an implicit conversion of the string to DATETIME and conversions from strings to datetime are deterministic only when specific styles are used.

7

I'm late to the party but this approach, while similar to @ypercube's answer, avoids the need to use any string conversion (which can be more expensive than date conversions), is deterministic, and should continue to work if MS ever change the default date value from 1900-01-01 (even though they probably wont change this):

DECLARE @D DATE = SYSUTCDATETIME()
, @T TIME = SYSUTCDATETIME();

SELECT DATEADD(DAY, DATEDIFF(DAY, @T, @D), CONVERT(DATETIME2, @T));

The principle is that by converting the time value to datetime2 and then to date, it strips the time out and assigns the default date, you then datediff this with your date value to get the days to add, cast your time to datetime2 and add the days on.

  • Instead of "DATEDIFF(DAY, @T, @D)" it should be "DATEDIFF(DAY, 0, @D)". The result is the same, but helps to avoid confusion. DateDiff(day, ...) casts arguments to lowest int number of days, so @T is converted to 0 anyway. – Dennis Gorelik Feb 28 at 7:27
5

For SQL Server 2012 and above there is the DATETIME2FROMPARTS function. It has this form:

DATETIME2FROMPARTS(year, month, day, hour, minute, seconds, fractions, precision)

For the given sample data this becomes

select Answer = DATETIME2FROMPARTS(2013, 10, 13, 23, 59, 59, 9999999, 7);

which results in

Answer
---------------------------
2013-10-13 23:59:59.9999999

The parts can be obtained using DATEPART() if starting from temporal datatypes, or from the text used to construct the sample values in the question.

0

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));
0
SELECT mydate=CAST(CAST(@D AS nvarchar(max)) + ' ' + 
                   CAST(@T AS nvarchar (max)) 
              AS DATETIME2);
  • 5
    Have you tested this? Does it work? Is it affected by language settings? – ypercubeᵀᴹ Jan 4 '17 at 7:44
0

I was looking for something else when I landed here. The question is pretty old, but there are some recent comments and activity. Thought I'd share a simple method that is very similar to the answer @Atario gave, but a bit shorter and some might find easier to read:

declare @d date = '2013-10-13'
declare @t time(7) = '23:59:59.9999999'

select cast(concat(@d, ' ', @t) as datetime2(7))
-3

You can truncate the with cast to DATE to truncate, then back to DATETIME to add the TIME

select CAST( cast(getdate() as date) as DATETIME)  + CAST(getdate() as TIME)
  • The trick is good, but it doesn't answer the question on the top. – peterh Jul 16 '18 at 1:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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