Substracting dates

system · November 14, 2003, 9:43am

hi ,

I need to calculate the elapsed time between two transactions. I have the times but I get error whe I try to substract them. I have created a new class in the designer with string type for my time. I got the time as string try to substring the hour , the minutes and the seconds, I am able to perform some operations but I guess this is deadend…

If any one has worked on this issue please give me some clues how to tackle this…

Thanks

Emile

Emile (BOB member since 2003-06-06)

system · November 14, 2003, 12:59pm

What DBMS (Oracle, IBM DB2, etc.) are you using?
Can’t you do the calculation at the universe or DB level instead?

Passing time and/or dates as STRINGS/CHARACTER is ususally not a good idea, because then you loose the correct sorting and cannot easily do time difference calculation IMHO.

Also, if you do a search on BOB there has been a thread with a formula how to calculate time differences within BO Reporter (down to the second).

Or take a look at this thread: Variance calculation

Andreas (BOB member since 2002-06-20)

system · November 14, 2003, 10:52pm

I recently offered a solution on tek-tips forum which may be of use to you.

It does look complicated but works quite nicely:

http://www.tek-tips.com/viewthread.cfm?SQID=692219&SPID=393&page=1

This is the bottom line:

For anyone else interested. This is a solution I mailed to Ainkca on ORACLE data with time and date coming in seperate fields. It takes into account that the later time is the next morning and it properly formats the resulting final difference as : ‘00:00:00’

Day_check = DaysBetween( ,)

seconds1 = ToNumber(SubStr(FormatDate( ,“HHmmss”) ,1 ,2))*3600+ToNumber(SubStr(FormatDate( ,“HHmmss”) ,3 ,2))*60+ToNumber(SubStr(FormatDate( ,“HHmmss”) ,5 ,2))

seconds2 = ToNumber(SubStr(FormatDate( ,“HHmmss”) ,1 ,2))*3600+ToNumber(SubStr(FormatDate( ,“HHmmss”) ,3 ,2))*60+ToNumber(SubStr(FormatDate( ,“HHmmss”) ,5 ,2))

Time diff = If (<Day_check>=0) Then - Else If (<Day_check> =1) Then (86400 -+) Else 0

Time diff format hour =FormatNumber(Truncate((/3600) ,0) , “00”)

Time diff format minute =FormatNumber(Truncate((-Truncate((/3600) ,0)*3600)/60 ,0) ,“0”)

Time diff format seconds =FormatNumber(()-ToNumber()*3600-ToNumber()*60 ,“0”)

Time diff final =If(Truncate((-Truncate((/3600) ,0)*3600)/60 ,0)<10) Then &":"&“0”&&":"& Else &":"&&":"&

blom0344 (BOB member since 2002-09-04)

system · November 17, 2003, 7:40am

Hi

Thanks a lot for the approach… finally I also came to this … converting everything in hours , substracting them and converting the result again in hours . min and sec…

I was hoping for a simpler solution but I guess that is the way …

Thanks a lot once again…

Emile

Emile (BOB member since 2003-06-06)

system · November 17, 2003, 10:24am

I guess you are working with ORACLE? In DB2 there is the TIMESTAMPDIFF function one can put to use in the universe to calculate the time difference between 2 timestamps. My guess is that a user defined function should be possible.

An example:

That should enable calculations outside the report, at the DB level…

blom0344 (BOB member since 2002-09-04)

system · November 17, 2003, 1:34pm

Remember: in the link I gave you Ken Gaul has written a DLL that you can add to BO, which will add a new BO Reporter function to calculate date/time differences.

Andreas (BOB member since 2002-06-20)

system · November 18, 2003, 9:00am

That may be a suitable solution , but would a function at database level not be more generic? Locally adding DLL’s may work fine technically, but having solution through the DP would be a lot easier to support and maintain…

blom0344 (BOB member since 2002-09-04)