I'm executing the following query.

declare @Offset as int = 3600
declare @Now as datetime = getdate()
declare @Then as datetime = dateadd(ss, @Offset, @Now)
declare @Diff as int = datediff(hh, @Now, @Then)
select @Now, @Then, @Diff

The objective is to examine how the server handles conversion to full hours given different number of seconds. For offset 3600, it should be 1 (which is confirmed). For 3000 it should be 0 or 1 (depending on whether it rounds or floors). According to the result, it is 0.

I tried 3599 expecting to get 0 but, to my surprise, I got 1. So I started to decrease gradually to find the threshold. Eventually, I reached 3000 and got 1! It was 0 before...

Then, I retried the exercise with 2400, getting 0. Iteratively lowering by about 20 (i.e. 2500, 2480, 2460, 2440, 2420) I repeatedly got 1. Finally, I reached 2400 and this time, it produced 1!

It seems that I gradually affect how the hour count is rounded, which sounds insane technically speaking. Still, I can't argue with the reality - it does happen.

What is the actual reason for the observed phenomenon?

CodePudding user response：

The objective is to examine how the server handles conversion to full hours given different number of seconds.

If the objective is to test it, why first wouldn't you consult the documentation?

DATEDIFF (Transact-SQL)
This function returns the count (as a signed integer value) of the specified datepart boundaries crossed between the specified startdate and enddate.

So A DATEDIFF of hour between two DateTime values (or DateTime2, or DateTimeOffset) will ONLY look at the LEFT part of the value up to the hours part of the date. Truncating the value to the hours, the seconds is removed for the purpose of the comparison.

SELECT DATEDIFF(hour, '2022-01-01 14:59:00', '2022-01-01 15:01')     --> 1
SELECT DATEDIFF(hour, '2022-01-01 14:01:00', '2022-01-01 14:59')     --> 0     
SELECT DATEDIFF(hour, '2022-01-01 13:59:59', '2022-01-01 15:00:59')  --> 2

For offset 3600, it should be 1 (which is confirmed). For 3000 it should be 0 or 1 (depending on whether it rounds or floors)

There is no rounding going on here at all, it is a simple truncation. This means that every second in between your tests, because you are using GetDate() the seconds component of the time during execution is advancing, which is reducing the number of seconds that is required to push the value you are evaluating into the next hour.

There is NOTHING random about the output at all! Every time you re-run your fiddle, some seconds have changed so the output will as well. DO NOT use 'GetDate()' as a proof for testing as that makes your test and the results bound to that specific point in time.

For this reason, if you need to calculate the Total Elapsed Hours between two times, you will need to use the next significant date part to the right and adjust your results: (for hour, then next most significant is minute)

SELECT DATEDIFF(minute, '2022-01-01 14:59:00', '2022-01-01 15:01') / 60.0     --> 0.033333
SELECT DATEDIFF(minute, '2022-01-01 14:01:00', '2022-01-01 14:59') / 60.0     --> 0.966666     
SELECT DATEDIFF(minute, '2022-01-01 13:59:59', '2022-01-01 15:00:59') / 60.0  --> 1.016666

How you deal with rounding in this case becomes entirely up to you.

I'm not sure this illustration is clear, or necessary, but you could run a query like this that evaluates different combinations of @Now and @Offset to compute the @Diff and @Then:

WITH Times AS (
    SELECT [Now]=CAST('2022-07-01 14:00' as datetime)
    UNION ALL
    SELECT [Now]=CAST('2022-07-01 14:30' as datetime)
    UNION ALL
    SELECT [Now]=CAST('2022-07-01 14:45' as datetime)
    UNION ALL
    SELECT [Now]=CAST('2022-07-01 14:55' as datetime)
)
, Interval as (
    SELECT offset = 3600
    UNION ALL
    SELECT offset = o.offset - 300
    FROM Interval o
    WHERE o.offset > 0
)
SELECT 
     [Now], [offset], [Then], [Diff]
FROM Times
CROSS JOIN Interval
CROSS APPLY (SELECT [Then] = dateadd(ss, Offset, [Now])) as func1
CROSS APPLY (SELECT Diff = datediff(hh, [Now], [Then])) as func2