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.
<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)
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)