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