给定以下组件

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对于加法运算符无效。



SELECT DATEADD(NANOSECOND,DATEDIFF(NANOSECOND,CAST('00:00:00.0000000' AS TIME),@T),@D)



datediff函数导致溢出。分隔两个日期/时间实例的日期部分的数量过多。尝试使用日期精度较低的datediff



*使用DATEDIFF_BIG可以避免Azure SQL数据库和SQL Server 2016中的溢出。


SELECT CAST(@D AS DATETIME) + @T 



数据类型datetime和time在加法运算符中不兼容。




SELECT CAST(@D AS DATETIME) + CAST(@T AS DATETIME)



返回结果但精度下降2013-10-13 23:59:59.997


#1 楼

这似乎可以正常工作并保持精度:

SELECT DATEADD(day, DATEDIFF(day,'19000101',@D), CAST(@T AS DATETIME2(7)))


CASTDATETIME2(7)TIME(7)值(@T)转换为日期部分为DATETIME2'1900-01-01',其中是日期和日期时间类型的默认值(请参阅MSDN上datetime2CAST页面上的CONVERT和注释*。)

* ...当仅表示日期或仅时间分量的字符数据被转换时设置为datetime或smalldatetime数据类型时,未指定的时间部分设置为00:00:00.000,未指定的日期部分设置为1900-01-01。

DATEADD()DATEDIFF()函数要小心其余的,即将1900-01-01DATE值之间的天数差(@D)相加。

在以下位置进行测试:SQL-Fiddle


@ Quandary,SQL Server认为上述表达式不是确定性的。如果我们要使用确定性表达式,例如因为要用于PERSISTED列,则需要将'19000101' **替换为0CONVERT(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)不确定,因为它会隐式地将字符串转换为DATETIME,并且从字符串到日期时间的转换只有在使用特定样式时才是确定性的。



#2 楼

我来晚了,但是这种方法虽然类似于@ypercube的答案,但是避免了使用任何字符串转换(可能比日期转换更昂贵)的需要,它是确定性的,并且如果MS更改了1900-01-01的默认日期值(即使他们可能不会更改此值):

DECLARE @D DATE = SYSUTCDATETIME()
, @T TIME = SYSUTCDATETIME();

SELECT DATEADD(DAY, DATEDIFF(DAY, @T, @D), CONVERT(DATETIME2, @T));


原理是通过将时间值转换为datetime2然后转换为日期,它会删除超时并分配默认日期,然后使用datediff将其与日期值一起获取要添加的日期,将时间转换为datetime2并添加日期。

评论


代替“ DATEDIFF(DAY,@T,@D)”,它应该是“ DATEDIFF(DAY,0,@D)”。结果是相同的,但有助于避免混淆。 DateDiff(day,...)将参数强制转换为int的最小天数,因此@T始终转换为0。

– Dennis Gorelik
19年2月28日在7:27

#3 楼

对于SQL Server 2012及更高版本,具有DATETIME2FROMPARTS函数。它具有以下形式:

DATETIME2FROMPARTS(year, month, day, hour, minute, seconds, fractions, precision)


对于给定的样本数据,它变为

select Answer = DATETIME2FROMPARTS(2013, 10, 13, 23, 59, 59, 9999999, 7);


,导致

Answer
---------------------------
2013-10-13 23:59:59.9999999


如果从时间数据类型或从用于构造问题中样本值的文本开始,则可以使用DATEPART()获得零件。

#4 楼

我登陆这里时正在寻找其他东西。这个问题已经很老了,但是最近有一些评论和活动。以为我会分享一种与@Atario给出的答案非常相似的简单方法,但是要简短一些,有些可能会更容易阅读:

declare @d date = '2013-10-13'
declare @t time(7) = '23:59:59.9999999'

select cast(concat(@d, ' ', @t) as datetime2(7))


评论


无法相信这行得通,但确实行得通。

–杰米·马歇尔(Jamie Marshall)
19年12月3日,19:20

CONCAT似乎是最干净的dba.stackexchange.com/a/258596/56120

–肯纳
20年1月31日在17:52

#5 楼

不让您的第一个示例工作对SQL Server来说是非常愚蠢的,这似乎也很愚蠢,但是…

select convert(datetime2, convert(nvarchar(max), @d) + ' ' + convert(nvarchar(max), @t));


#6 楼

SELECT mydate=CAST(CAST(@D AS nvarchar(max)) + ' ' + 
                   CAST(@T AS nvarchar (max)) 
              AS DATETIME2);


评论


你测试过了吗?它行得通吗?它受语言设置的影响吗?

–超立方体ᵀᴹ
17年1月4日在7:44

#7 楼

您可以截断with强制转换为DATE进行截断,然后返回DATETIME以添加TIME

select CAST( cast(getdate() as date) as DATETIME)  + CAST(getdate() as TIME)


评论


诀窍很好,但是并不能回答最上面的问题。

–peterh-恢复莫妮卡
18年7月16日在1:33