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

sql-server add tag

Martin Smith (imported from SE)

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`][1].

---

    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`


  [1]: https://msdn.microsoft.com/en-GB/library/mt628058.aspx

Top Answer

ypercubeᵀᴹ (imported from SE)

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`][1] and the comment in the [`CAST` and `CONVERT`][2] documentation:

> 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()`functions 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][3]

---
As noticed by [Quandary][4], the above expression is considered non-deterministic 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][5] only when specific styles are used.

[1]: https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime2-transact-sql
[2]: https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql
[3]: https://sqlfiddle.com/#!3/e50371/2
[4]: https://dba.stackexchange.com/users/5092/quandary
[5]: https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/deterministic-and-nondeterministic-functions

Answer #2

Brian Jorden (imported from SE)

Thought I'd share a simple method that is 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 #3

knuckles (imported from SE)

This approach, while similar to [ypercube's answer][1], avoids the need to use any string conversion (which can be more expensive than date conversions), is deterministic, and should continue to work if Microsoft ever changes 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));

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.

  [1]: https://topanswers.xyz/databases?q=1777#a2003

Answer #4

Michael Green (imported from SE)

For SQL Server 2012 and above there is the [DATETIME2FROMPARTS][1] 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()][2] if starting from temporal datatypes, or from the text used to construct the sample values in the question.

  [1]: https://docs.microsoft.com/en-us/sql/t-sql/functions/datetime2fromparts-transact-sql
  [2]: https://docs.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql

4 Answers