DECLARE @D DATE = '2013-10-13' DECLARE @T TIME(7) = '23:59:59.9999999'
将它们组合起来生成DATETIME2(7)结果的最佳方法是什么？值为’2013-10-13 23：59：59.9999999’？
下面列出了一些不起作用的东西.
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.
*使用DATEDIFF_BIG可以避免Azure sql数据库和sql Server 2016中的溢出.
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
解决方法
SELECT DATEADD(day,DATEDIFF(day,'19000101',@D),CAST(@T AS DATETIME2(7)))
CAST到DATETIME2(7)将TIME(7)值(@T)转换为DATETIME2,其中日期部分为’1900-01-01′,这是日期和日期时间类型的默认值(请参阅datetime2和注释) *在MSDN的CAST and CONVERT页面.)
* …当仅表示日期或仅表示时间组件的字符数据被强制转换为datetime或smalldatetime数据类型时,未指定的时间组件设置为00：00：00.000,未指定的日期组件设置为1900-01- 01.
DATEADD()和DATEDIFF()函数负责其余部分,即添加1900-01-01和DATE值(@D)之间的天数差异.
测试时间：SQL-Fiddle
正如@Quandary所注意到的,sql Server认为上述表达式不具有确定性.如果我们想要一个确定性表达式,比如因为它将用于PERSISTED列,那么’19000101’**需要被0或CONVERT(DATE,’19000101′,112)替换：
CREATE TABLE date_time
( d DATE NOT NULL,t TIME(7) NOT NULL,dt AS DATEADD(day,CONVERT(DATE,112),d),CAST(t AS DATETIME2(7))
) PERSISTED
) ;
**：DATEDIFF(day,d)不是确定性的,因为它将字符串隐式转换为DATETIME,并且仅当使用特定样式时,从字符串到datetime的转换才是deterministic.
