Calculate number of hours between 2 dates - Cognos Impromptu

[thierryverstraete]

Dear user community,

I have 2 database fields that consist of date/time formats. I want to see the time difference expressed in hours when I subtract the "recent" date from the "older" date.

Example 1 --> 08/11/2009 16:00 - 08/11/2009 14:00 should result in 2 hours
Example 2 --> 08/11/2009 16:00 - 08/10/2009 14:00 should result in 26 hours

I do not care about a minute precision...in case the time difference also includes minutes then =< 30 minutes can be rounded down and > 30 minutes can be rounded up.

Example 1 --> time difference of 1 hour and 40 minutes should result in 2 hours
Example 2 --> time difference of 1 hour and 5 minutes should result in 1 hour

Thanks so much!!!

 

[drlex]

Thierry,
You neglect to mention the Cognos program you are using.
If it is Impromptu, would not the 'hours-between' function be apposite for your needs?
However, since minutes are ignored, it might be best to use the 'minutes-between' and add 29 so as to round up before doing an integer divide.
Thus:

cast_integer(((minutes-between( [start_date],[end_date])+29) / 60)

(If you have a need to be uber-precise about how 1:29:59 is considered to be 1 hour and not 2, you could always use the third function in the series, 'seconds-between', and divide by 3600 )


soi la, soi carré

 

[drlex]

Apologies - I overlooked the words "Cognos Impromptu" in the thread title!

soi la, soi carré