给定以下组件
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
操作数数据类型日期对于加法运算符无效。
SELECT CAST(@D AS DATETIME2(7)) + @T
操作数数据类型 datetime2 对 add 运算符无效。
SELECT DATEADD(NANOSECOND,DATEDIFF(NANOSECOND,CAST('00:00:00.0000000' AS TIME),@T),@D)
datediff 函数导致溢出。分隔两个日期/时间实例的日期部分的数量太大。尝试将 datediff 与不太精确的日期部分一起使用。
* 在 Azure SQL 数据库和 SQL Server 2016 中,使用DATEDIFF_BIG
.
SELECT CAST(@D AS DATETIME) + @T
数据类型 datetime 和 time 在 add 运算符中不兼容。
SELECT CAST(@D AS DATETIME) + CAST(@T AS DATETIME)
返回结果但丢失精度
2013-10-13 23:59:59.997
这似乎工作并保持精度:
CAST
to将值 ( )DATETIME2(7)
转换为 date 部分所在的a ,这是 date 和 datetime 类型的默认值(请参阅MSDN上的注释*和页面。)TIME(7)
@T
DATETIME2
'1900-01-01'
datetime2
CAST
CONVERT
* ... 当仅表示日期或仅表示时间分量的字符数据转换为 datetime 或 smalldatetime 数据类型时,未指定的时间分量设置为 00:00:00.000,未指定的日期分量设置为 1900-01- 01 .
DATEADD()
andDATEDIFF()
函数负责其余部分,即添加 和 值 ( ) 之间的天1900-01-01
数DATE
差@D
。测试:SQL-Fiddle
正如@Quandary所注意到的,上述表达式被 SQL Server 认为是不确定的。如果我们想要一个确定性表达式,比如说因为它要用于
PERSISTED
列,则'19000101'
**需要替换为0
orCONVERT(DATE, '19000101', 112)
:**:
DATEDIFF(day, '19000101', d)
不是确定性的,因为它对字符串的隐式转换DATETIME
以及从字符串到日期时间的转换仅在使用特定样式时才具有确定性。我迟到了,但这种方法虽然类似于@ypercube 的回答,但避免了使用任何字符串转换(这可能比日期转换更昂贵)的需要,是确定性的,并且如果 MS 改变了应该继续工作1900-01-01 的默认日期值(即使他们可能不会更改此值):
原理是,通过将时间值转换为 datetime2 然后再转换为日期,它会去除超时并分配默认日期,然后您可以使用您的日期值对它进行 datediff 以获取要添加的天数,将您的时间转换为 datetime2 并添加天。
对于 SQL Server 2012 及更高版本,有DATETIME2FROMPARTS函数。它有这种形式:
对于给定的样本数据,这变成
这导致
如果从时间数据类型开始,或者从用于构造问题中的样本值的文本开始,则可以使用DATEPART()获得这些部分。
当我降落在这里时,我正在寻找其他东西。这个问题已经很老了,但是最近有一些评论和活动。以为我会分享一个与@Atario 给出的答案非常相似的简单方法,但要短一些,有些人可能会觉得更容易阅读:
我已将两个值(@D 和 @T)都转换为二进制,我将二进制值连接起来,然后再转换回 DT2(7):
结果:
或者,更简洁:
该
time(7)
值被转换为binary(6)
而不是记录的 5 字节存储大小,因为在转换为二进制时,SQL Server 会添加一个精度字节前缀以确保可以进行往返转换。这在但不是的脚注中记录。datetime2
time
在这种情况下,简单的连接有效,因为
time
精度与所需的datetime2
精度相同。如果不是这样,您需要删除精度字节并将其替换为所需的值。此方法依赖于可能更改的实现细节,因此未记录且不受支持。
db<>小提琴
SQL Server 不让你的第一个示例工作是相当愚蠢的,这看起来也很愚蠢,但是……