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

PhilippeBertrand%20_SAP_'s gravatar image

PhilippeBert...
1.5k41735
accept rate: 22%

edited 30 Oct '13, 14:43

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

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • 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:

×203
×2

question asked: 30 Oct '13, 13:46

question was seen: 3,109 times

last updated: 01 Nov '13, 07:17