on 2013 Oct 30 1:46 PM
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?
Request clarification before answering.
User | Count |
---|---|
52 | |
10 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.