DateAdd副作用？

Question

在Microsoft 2016 (SP2-CU15)中，我遇到了一种非常奇怪的行为：

select convert(datetime, max(TS) + 1.0/24) as A 
from table;

产率2021-01-16 11:59:00.000

而

select convert(datetime, max(TS) + 1.0/24) as A
     , dateadd(hour, 1, max(TS)) as B
from table;

给我A的2021-01-16 11:58:59.943 (B的2021-01-16 11:59:00.000 )。因此，在我看来，添加第二列会改变第一列的结果？！

我可以通过将1.0转换为real (顺便说：convert(datetime, max(TS) + cast(1.0 as real)/24) )来强制两列版本工作，但我可以而不是，通过编写convert(datetime, max(TS) + cast(1.0 as float)/24)强制一列版本失败。

知道这里发生了什么事吗？

谢谢!

亨德里克。

更新:根据要求，下面是一个很小的例子：

CREATE TABLE TestTS (TS FLOAT);
INSERT INTO TestTS (TS) VALUES (44210.4993055556);

SELECT convert(datetime, max(TS) + 1.0/24) as A
    , dateadd(hour, 1, max(TS)) as B
from TestTS

如前所述，如果将B列注释掉，则A的值将发生变化.

Answer 1

原因

感谢@MartinSmith给出的线索。

原因是查询自动参数化和选择存储值的数据类型。

查询1是自动参数化的：

StatementText="SELECT CONVERT([datetime],MAX([TS])+@1/@2)
....
<ColumnReference Column="@2" ParameterCompiledValue="(24)" ParameterRuntimeValue="(24)" />
<ColumnReference Column="@1" ParameterCompiledValue="(1.0)" ParameterRuntimeValue="(1.0)" />

查询2是而不是自动参数化：

StatementText="SELECT convert(datetime, max(TS) + 1.0/24) as A...."

发生这种情况的原因是第一个查询，而不是第二个查询，这有点不可思议。

来自Server数据类型页面：

当使用+、-、*、/或%算术运算符执行int、bigint、tinyint或bigint常量值到浮点数、实值、十进制或数字数据类型的隐式或显式转换时，Server在计算数据类型和表达式结果的精度时应用的规则因查询是否自动命名而有所不同。 因此，查询中类似的表达式有时会产生不同的结果。当查询未自动命名时，常量值首先转换为数值，其精度仅足以容纳常量的值，然后再转换为指定的数据类型。例如，常量值1被转换为numeric (1, 0)，常数值250被转换为numeric (3, 0)。 当查询被自动命名时，常量值总是在转换为最终数据类型之前被转换为numeric (10, 0)。当涉及/运算符时，不仅结果类型的精度在类似的查询中不同，而且结果值也可能不同。例如，包含表达式SELECT CAST (1.0 / 7 AS float)的自动数组化查询的结果值将与未自动命名的同一查询的结果值不同，因为自动数组化查询的结果将被截断以适应numeric (10, 0)数据类型。

效果

基于上述，使用了以下数据类型(请参阅：精度、比例和长度(Transact-SQL)，了解如何计算结果类型)：

查询1提供了更高的精度：

NUMERIC( 2, 1 ) / NUMERIC( 10, 0 ) = NUMERIC( 13, 12 )

查询2：

NUMERIC( 2, 1 ) / NUMERIC( 2, 0 ) = NUMERIC( 7, 6 )

解决方案

将您的文字和/或中间结果转换为所需的类型，以避免意外。在您的具体情况下，最好的解决方案不是像Panagiotis Kanavos在他的回答中解释的那样使用数字算法来操作日期。

或者，强制使用浮点数据类型(按data注释) convert(datetime, max(TS) + 1e/24)也能做到这一点。

这问题处理的是同一个问题。

Answer 2

DATEADD没有什么问题。问题在于问题的其余部分。

首先，有一个关键的错误。日期以浮动形式存储。应该使用适当的类型(如datetime2、datetime或datetimeoffset )。假设不需要毫秒精度，最好的选择是datetime2(0)或datetimeoffset(0)。

datetime本质上是一种遗留类型，其内部存储格式是.OADate格式的浮点数。但这并不意味着应该使用浮点数来代替正确的类型，只应该使用varbinary而不是int或bigint。

然后，通过计算1小时的浮点值( 1/24 )，尝试将1小时添加到OADate值。不过，这是一个无理数(0.04166666666.)这意味着舍入错误总是导致不准确的值。

溶液

真正的解决方案是使用正确的类型和DATEADD，例如：

CREATE TABLE TestTS (TS datetime2(0));
INSERT INTO TestTS (TS) VALUES ('2021-01-16 10:59:00.000');

SELECT dateadd(hour, 1, max(TS)) as B
from TestTS

如果您想要毫秒精度，请使用datetime2(3)。

让黑客开始工作.

如果您使用了datetime，您最终将不需要转换为日期时间，但是结果仍然不精确。这是：

declare @TestTS table (TS datetime);
INSERT INTO @TestTS (TS) VALUES ('2021-01-16 10:59:00.000');

SELECT max(ts)+ (1.0/24)
from @TestTS

产生2021-01-16 11:58:59.943。黑客最初看起来起作用的唯一原因可能是转换过程中的舍入错误。

通过添加浮点数获得正确结果的唯一方法是将精度提高到8个小数位数：

declare @TestTS table (TS datetime);
INSERT INTO @TestTS (TS) VALUES ('2021-01-16 10:59:00.000');

SELECT max(ts)+ (1.00000/24)--, dateadd(hour, 1, max(TS)) as B
from @TestTS

这会产生2021-01-16 11:59:00.000。

1.0是小数(2，1)。the根据操作数的函数位来计算小数除法的小数.如果操作数最多有4个小数位数，结果将有6个小数位数，这是不够的。任何超过4的小数都要加1位。1.00000的结果是8小数0.04166666

不过别这么做。