cancel
Showing results for 
Search instead for 
Did you mean: 

datediff behaviour

philippefbertrand
Participant
4,422

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?

Accepted Solutions (0)

Answers (0)