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

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.