Subject Re: [firebird-support] DATEDIFF(HOUR...) returns 1 for a 45 minutes interval
Author Mark Rotteveel
On 4-10-2014 14:11, venussoftop@... [firebird-support] wrote:
> I would have attached a screen shot of the sample data but I guess that
> is not possible. What I am trying to achieve right now is get the time
> difference in hh:mm format to the user

This behavior is documented at

DATEDIFF doesn't look at any smaller units than the one specified in the
first argument. As a result,
* “datediff (year, date '1-Jan-2009', date '31-Dec-2009')” returns 0, but
* “datediff (year, date '31-Dec-2009', date '1-Jan-2010')” returns 1
> Please see the 4th row, the diff between 11:30 and 12:15 is 1 Hour and
> -15 mins whereas difference between 10:00 and 10:45 is 0 hour and 45 mins.

It only looks at hour fields (and higher). The difference between 11 and
12 is 1.

> Please advise on what I need to do correctly to get 0 hour and 45 mins
> for both 2nd and 4th row.

You need to diff minutes only and divide by 60 for hours. If you want
something 1 hour and 30 minutes for 90 minutes, you need to use modulus 60.

Mark Rotteveel