DateAdd side effects?

Question

I am experiencing a very strange behavior here with Microsoft SQL Server 2016 (SP2-CU15):

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

yields 2021-01-16 11:59:00.000

while

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

gives me 2021-01-16 11:58:59.943 for A (and 2021-01-16 11:59:00.000 for B). So, it seems to me that adding the second column changes the result for the first?!

I can force the two-column version to work by casting 1.0 to real, btw: convert(datetime, max(TS) + cast(1.0 as real)/24), but I can not force the one-column version to fail by writing convert(datetime, max(TS) + cast(1.0 as float)/24).

Any ideas what's happening here?

Thanks!

Hendrik.

Update: As requested, here is a minimal example:

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

As described, if you comment out the B-column, the value of A changes.

user_134204 · Answer

There's nothing wrong with DATEADD. The problem is the rest of the question.

First, there's a critical bug. Dates are stored as floats. An appropriate type should be used instead, eg datetime2, datetime or datetimeoffset. The best options are datetime2(0) or datetimeoffset(0), assuming no millisecond precision is needed.

datetime is essentially a legacy type, whose internal storage format is ... a float in the OADate format. That doesn't mean floats should be instead of the correct type though, no more than varbinary should be used instead of int or bigint.

Then, there's an attempt to add one hour to the OADate value, by calculating the floating point value of 1 hour in that format, 1/24. That's an irrational number though (0.04166666666....) which means that rounding errors always result in an inaccurate value.

Solution

The real solution is to use the correct type and DATEADD, eg :

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

If you want millisecond precision, use datetime2(3).

Getting the hack to work.

If you used datetime you wouldn't need to convert to datetime in the end, but the result would still be imprecise. This :

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

Produces 2021-01-16 11:58:59.943. The only reason the hack looked to be working in the first place was probably due to rounding errors during conversion.

The only way to get a correct result by adding floating point numbers is to increase precision to 8 fractional digits :

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

That produces 2021-01-16 11:59:00.000.

1.0 is a decimal(2,1). T-SQL calculates the fractional digits of decimal division based on the functional digits of the operands. If the operands have up to 4 fractional digits, the result will have 6 fractional digits, which isn't enough. 1 digit is added for any fractional digit above 4. 1.00000 results in 8 fractional digits 0.04166666

Don't do this though.

user_6305294 · Answer

Cause

Thanks to @MartinSmith for the clue.

The cause is query auto-parameterization and the data types being chosen to store values.

Query 1 is auto-parameterized:

StatementText="SELECT CONVERT([datetime],MAX([TS])+@1/@2)
....

Query 2 is not auto-parameterized:

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

Why it happens is the first query and not the second query is a bit of a black magic.

From SQL Server data types page:

When you use the +, -, *, /, or % arithmetic operators to perform implicit or explicit conversion of int, smallint, tinyint, or bigint constant values to the float, real, decimal or numeric data types, the rules that SQL Server applies when it calculates the data type and precision of the expression results differ depending on whether the query is autoparameterized or not.

Therefore, similar expressions in queries can sometimes produce different results. When a query is not autoparameterized, the constant value is first converted to numeric, whose precision is just large enough to hold the value of the constant, before converting to the specified data type. For example, the constant value 1 is converted to numeric (1, 0), and the constant value 250 is converted to numeric (3, 0).

When a query is autoparameterized, the constant value is always converted to numeric (10, 0) before converting to the final data type. When the / operator is involved, not only can the result type's precision differ among similar queries, but the result value can differ also. For example, the result value of an autoparameterized query that includes the expression SELECT CAST (1.0 / 7 AS float) will differ from the result value of the same query that is not autoparameterized, because the results of the autoparameterized query will be truncated to fit into the numeric (10, 0) data type.

Effect

Based on the above, the following data types are used (refer to See: Precision, scale, and Length (Transact-SQL) for explanation of how result types are calculated):

Query 1 gives higher precision:

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

Query 2:

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

Solution

Cast your literals and / or intermediate results to the desired type to avoid surprises. In your specific case, best solution is not to use number arithmetic to manipulate dates as Panagiotis Kanavos explains in his answer.

Alternatively, forcing float data types (per Dan Guzman comment) convert(datetime, max(TS) + 1e/24) would do the trick as well.

This question deals with the same issue.

DateAdd side effects?

2 Answers

Cause

Effect

Solution