Subject Re: [firebird-support] DATEDIFF(HOUR...) returns 1 for a 45 minutes interval
Author Venus Software Operations
Thanks Mark, MOD() was very useful else I had to do calculations twice.

Kind regards

On 04-10-2014 09:15, Mark Rotteveel mark@... [firebird-support] wrote:

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


Thanking you.

Yours Faithfully,
For Venus Software Operations
Mr. Bhavbhuti Nathwani
Softwares for Indian Businesses at:


Please note: We reserve complete rights for policy changes in the future and the same will be applicable immediately as and when made.  Attachments may get corrupted before reaching you, in such a situation please let us know and we will resend you the same at the earliest.  We do not take any responsibility for data loss of any type and kind.  Data safety remains the sole the responsibility of the users of our softwares.

Internet email confidentiality:

This message may contain information that may be privileged or confidential.  If you are not the addressee nor are you responsible for the delivery of the message to the addressee indicated in this email, then you may not copy or deliver this email to anyone and you should notify the sender by reply email and then destroy this message.

Please reply email immediately to this message with REMOVE in the subject, if you or your employer do not consent to email of this kind.

Opinions, conclusions and other information in this message that do not relate to the official business of my firm shall be understood as neither given nor endorsed by my company.