The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

# datediff behaviour

 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? asked 30 Oct '13, 13:46 PhilippeBert... 1.4k●4●17●33 accept rate: 22% 1 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 (31 Oct '13, 04:33) Reimer Pods 1 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. (01 Nov '13, 07:15) Breck Carter
Be the first one to answer this question!
 toggle preview community wiki:

By Email:

Markdown Basics

• *italic* or _italic_
• **bold** or __bold__
• image?![alt text](/path/img.jpg "title")
• 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:

×186
×2

question asked: 30 Oct '13, 13:46

question was seen: 3,047 times

last updated: 01 Nov '13, 07:17