datediff behaviour

[philippefbertrand]

In reading the datediff docs, I expected that if the difference isn't an hour, there would be truncation, however what I observed is that the result is dependent on the time of day of the values.

select datediff( hour, dateadd( hour, 1, '2013-10-30 05:00:00.000'), cast( '2013-10-30 04:59:59.999' as datetimeoffset )) as a,
datediff( hour, dateadd( hour, 1, '2013-10-30 04:59:59.999'), cast( '2013-10-30 05:00:00.000' as datetimeoffset )) as b,
datediff( hour, dateadd( hour, 1, '2013-10-30 05:00:00.000'), cast( '2013-10-30 05:00:00.001' as datetimeoffset )) as d,
datediff( minute, dateadd( hour, 1, '2013-10-30 05:00:00.000'), cast( '2013-10-30 04:59:59.999' as datetimeoffset )) as ma,
datediff( minute, dateadd( hour, 1, '2013-10-30 04:59:59.999'), cast( '2013-10-30 05:00:00.000' as datetimeoffset )) as mb,
datediff( minute, dateadd( hour, 1, '2013-10-30 05:00:00.000'), cast( '2013-10-30 05:00:00.001' as datetimeoffset )) as md

dbisql on a SQL Anywhere 16 database gives:

a,b,d,ma,mb,md
-1,0,-1,-61,-59,-60

I expected (where x is .001 of a second in whatever units)

a=-(1+x) truncated to -1
b=-(1-x) truncated to 0
d=-(1-x) truncated to 0   <- which doesn't happen
ma=-(60+x) truncated to -60   <- which doesn't happen
mb=-(60-x) truncated to -59
md=-(60-x) truncated to -59   <- which doesn't happen

Docs say:

This function calculates the number of date parts between two specified dates. The result is a signed integer value equal to (date-expression-2 - date-expression-1), in date parts.

The DATEDIFF function results are truncated, not rounded, when the result is not an even multiple of the date part.

Is this expected behaviour?

[reimer_pods]

Though I can't answer your question, I've remembered Breck's blog covering that topic:
http://sqlanywhere.blogspot.de/2013/09/beware-datediff-alternatives.html

[Breck_Carter]

The fact that select datediff ( hour, '2013-10-30 06:00:00.000', '2013-10-30 05:00:00.001' ) returns -1 instead of 0 is unexpected. It doesn't start returning zero until the second timestamp is '2013-10-30 05:01:00.000' which is one more reason to not trust DATEDIFF with anything important.

The actual (as opposed to documented) behavior for MINUTE is different from HOUR, as described here: http://sqlanywhere.blogspot.ca/2013/07/documenting-datediff.html ...beware "minute boundaries" as opposed to "minutes".

That article is followed by a whole series about this problem on my blog.