How to combine date from one field with time from another field - MS SQL Server

The existing answers do not address the `datetime2` datatype so I will add mine:

Assuming that you want to add a [`time`][1] value to a [`datetime2`][2] value where:

- The `datetime2` value could contain non-zero time component and/or fractional seconds
- The `time` value could contain the value `23:59:59.9999999` which is 86,399.9999999 seconds, 86,399,999,999.9 microseconds or 86,399,999,999,900 nanoseconds¹

Due to the limitations of [`dateadd`][3] function¹ you must add them in two steps:

- Convert the time value to seconds and use `dateadd(second, ...)`
- Extract the nanoseconds from the time value and use `dateadd(nanosecond, ...)` to add them to the date calculated above

```
declare @dv datetime2 = '2000-01-01 12:34:56.7890123';
declare @tv time = '23:59:59.9999999';
select dateadd(
nanosecond,
datepart(nanosecond, @tv),
dateadd(
second,
datepart(hour, @tv) * 60 * 60 + datepart(minute, @tv) * 60 + datepart(second, @tv),
@dv
)
);
-- 2000-01-02 12:34:56.7890122
```

---

¹ Nanosecond values might not fit in [`int`][4] datatype which `dateadd` function expects.


[1]:

[To see links please register here]

[2]:

[To see links please register here]

[3]:

[To see links please register here]

[4]:

[To see links please register here]