How to combine date and time to datetime2 in SQL Server?

Question

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

Answer 1

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

Answer 2

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.

Answer 3

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.

Answer 4

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

Answer 5

I have converted both values (@D and @T) to binary, I have concatenated binary values and then another conversion back to DT2(7):

SET NOCOUNT ON;

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

SELECT CONVERT(BINARY(6), @T) AS T, CONVERT(BINARY(3), @D) AS D
SELECT CONVERT(BINARY(9), @DT2) AS DT2
SELECT CONVERT(BINARY(6), @T) + CONVERT(BINARY(3), @D) AS DT2_BinaryConcat

DECLARE @DT2_AsBinary BINARY(9), @DT2_AsDateTime2 DATETIME2(7);
SET @DT2_AsBinary = CONVERT(BINARY(6), @T) + CONVERT(BINARY(3), @D);
SET @DT2_AsDateTime2 = CONVERT(DATETIME2(7), @DT2_AsBinary);
SELECT @DT2_AsDateTime2 AS DT2_Converted;

Results:

T              D
-------------- --------
0x07FFBF692AC9 0xB1370B

DT2
--------------------
0x07FFBF692AC9B1370B

DT2_BinaryConcat
--------------------
0x07FFBF692AC9B1370B

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

Or, more compactly:

DECLARE 
    @D date = '2013-10-13',
    @T time(7) = '23:59:59.9999999';

SELECT DT2 =
    CONVERT(datetime2(7), 
        CONVERT(binary(6), @T) + 
        CONVERT(binary(3), @D));

The time(7) value is converted to binary(6) rather than the documented storage size of 5 bytes because when converting to binary, SQL Server prefixes a precision byte to ensure round-trip conversion is possible. This is documented in a footnote for datetime2 but not time.

Simple concatenation works in this case because the time precision is the same as the desired datetime2 precision. If it were otherwise, you'd need to remove the precision byte and replace it with the required value.

This method relies on implementation details that could change and so is undocumented and unsupported.

db<>fiddle

Answer 6

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