BusinessObjects Board

Date difference in days, hours and minutes

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( ,)

=(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 :us: (BOB member since 2012-09-03)


A similar solution on the DeskI side:

(see my 2nd post in the topic)

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

Oh, well…I thought I was the first one :slight_smile: 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 :us: (BOB member since 2012-09-03)