Date difference in days, hours and minutes

[system]

I needed to update a report to show not only the difference between two dates in days but also in hours and minutes. I went two directions - asked an Oracle developer to help me find a function (tried online for about 15 minutes, could not find adequate Oracle function) and I also tried to find a workaround in Deski (BOXI 3.1 SP6).
I spent 2-3 hours trying to manipulate the data and finally figured out what I think is decent solution. This involves a lot of calculation at the report level so the report is now slower but the user really does not care. Here is how I did it.

=DaysBetween( ,)

<var_min>
=(ToNumber(Right(FormatDate( ,“HH:mm”) ,2)) + (ToNumber(Left(FormatDate( ,“HH:mm”) ,2))*60)) -
(ToNumber(Right(FormatDate( ,“HH:mm”) ,2)) + (ToNumber(Left(FormatDate( ,“HH:mm”) ,2))*60)) + (2460)

=Truncate(/60/24 ,0) = Truncate((- (*1440))/60 ,0) =-(*1440)-(*60) =& ":" & & ":" & =Truncate(Average()/60/24 ,0) &":" & Floor(Mod(Average()/60 ,24))&":" & Round(Average() ,0)

Now, the Oracle developer gave me back a query that looks just as complex as my solution here, if not more.

My questions are:

• has anyone found a simpler solution?
• How does this look on BO 4.0?
• What is the best Oracle function to use to update universe objects instead of doing this at the report level?

Thanks a lot, I have always been grateful for this forum!

---

ras (BOB member since 2012-09-03)

[system]

Hi,

A similar solution on the DeskI side:

Diff between two dates

I am trying to create a variable in Desktop Intelligence XI that will give me the diff between two dates. I used “=DaysBetween(<Admit Date & Time> ,<Order Date & Time>)” and I get just days. Is there a way to get his broke down to hours, minutes and seconds? djohnson (BOB member since 2007-07-26)

(see my 2nd post in the topic)

---

Marek Chladny (BOB member since 2003-11-27)

[system]

Oh, well…I thought I was the first one Thanks, I see that the solution they had was very similar to mine…shame I could not find it before I spent a few hours doing mine.
Still, if someone knows Oracle function that does this, I would like to know. Also, is there a new function in BO 4.x that helps with this?

---

ras (BOB member since 2012-09-03)