|
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:
Is this expected behaviour? |
Follow this question
By Email:Once you sign in you will be able to subscribe for any updates here
By RSS:Markdown Basics
- *italic* or _italic_
- **bold** or __bold__
- link:[text](http://url.com/ "title")
- image?
- numbered list: 1. Foo 2. Bar
- to add a line break simply add two spaces to where you would like the new line to be.
- basic HTML tags are also supported
Question tags:
question asked: 30 Oct '13, 13:46
question was seen: 4,059 times
last updated: 01 Nov '13, 07:17
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
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.