I observe a strange (incorrect?) calculation in minutes (SQL Anywhere Server Version 17.0.10.5963):
ClockOn = 09:59:59.999ClockOff = 10:00:00.001 datediff(millisecond, ClockOn, ClockOff) = 2 : OK datediff(second, ClockOn, ClockOff) = 0 : OK datediff(hour, ClockOn, ClockOff) = 0 : OK datediff(minute, ClockOn, ClockOff) = 1 : Why?
"The DATEDIFF function results are truncated, not rounded, when the result is not an even multiple of the date part." It seems that if we write minute, then first the time is truncated to minutes without rounding (“truncated, not rounded”), and then the difference is calculated. I couldn't find a counterexample.
asked 15 Jan, 05:01 Ilia63 
It's not really my area but I do have records of discussions about it from 2015 :). As of that time: SQLA implements DATEDIFF and related functions in a way that is not compatible with IQ / ASE and also in a way that is internally inconsistent. While this appears to be unintended, the behavior has been unchanged since the first implementation (Fall of 1992). When taking the difference between two datetime values in a given unit (datepart), there are two semantics implemented:
Consider two timestamps t1=’20151231 23:59:59.999999’ and ’20160101 00:00:00’. These are only one microsecond apart but they appear on different days / months and years. Under truncation semantics, DATEDIFF( pp, t1, t2 ) is zero for all date parts except microsecond. The difference (1 microsecond) is computed and divided by the larger unit with integer truncation to zero. Under boundary semantics, the result for all dateparts would be 1. ASE IQ SQLA MS1 year ? ? B B quarter ? ? B B month B B B B week B B B B day B B B B dayofyear ? ? B B hour T T T B minute T T B B second T T T B millisecond T T B B microsecond T T B/T B The SQLA semantics do not match ASE/IQ nor do they match MS. They are internally inconsistent because they switch between interpretations within the hour/minute/second range. There are related function in SQLA, and these are also not always consistent with the DATEDIFF results:  DAYS  B  HOURS  T  MINUTES  B  SECONDS  B (!) In HANA there is no support for DATEDIFF. There is a DAYS_BETWEEN (B), SECONDS_BETWEEN (T), and NANO100_BETWEEN(B/T). In the SQL standard, the issue of boundaries and truncation is largely avoided because arithmetic on datetime values yields an INTERVAL type and the operation is required to have an explicit class of interval (YEAR to MONTH or DAY TO SECOND) and optional precision/scale for the components. Truncation or detection of boundaries is fully under the user’s control. There were discussions about adding extra parameters, backward compatibility issues if we just changed the semantics, etc. I will leave it as an exercise to the reader to figure out where we ended up... answered 15 Jan, 09:23 John Smirnios Thanks for clarifying. ps If we add the date to the time, then, unfortunately, nothing changes.
A little "annoying" is the fact that the difference in seconds is 0, and the difference in minutes is still 1.
(15 Jan, 09:40)
Ilia63
Replies hidden
1
There was also this note in the old email: " If truncation semantics are desired, they can be computed using DATEDIFF( microsecond, t1, t2, ) / (integer number of microseconds in datepart)." So that should be DATEDIFF( microsecond, t1, t2 ) / 60000000 for getting minutes with Truncation semantics.
(15 Jan, 09:50)
John Smirnios
1
Just for the record, Breck had posted about those details in his blog, too (of course):
(16 Jan, 11:14)
Volker Barth
Replies hidden
