In a message dated 98-09-17 11:52:08 EDT, you write:
For our current project, we need to calculate a time
duration at the level of days, hours & seconds – a calculation which needs to be performed WITHIN BO, or otherwise through a stored procedure.
In other words, any help on time duration calculations would be tremendously appreciated! I must admit that it came as a surprise to me that BO does not allow substraction of two timestamps (‘incorrect data type’ error) - or did I overlook something here ?
Following is a post I sent to the list a while back regarding date math. It involves setting up your two dates as character strings, breaking apart the pieces, converting to numbers, and processing. I didn’t say it would be easy, but it can be done. The sample code shown returns a decimal number; if you need to get back to days, hours, minutes, and seconds, you will have to reparse the decimal into appropriate time values.
If you have the opportunity to do it in your database, that is a better place. More straightforward, I am sure.
Regards,
Dave Rathbun
Integra Solutions
www.islink.com
In a message dated 98-06-17 12:29:07 EDT, you write:
Using only BOI 4.1, I need to be able to subtract 2 dates to get a decimal number of days. I can’t use the DaysBetween function as if returns only an
integer value. Does anyone know of a way to accomplish what I need here?
Thanks in advance!!!
As you have found out, the DaysBetween ignores the time element of any date objects. You are, of course, not the first person to complain about this.
Several other folks have suggested doing the date math on the database level. This assumes you can either a) create an object using the Designer application, or b) that you will create a User Objects. If you can do the object in the universe design (rather than a UDO) that is the preferred solution. However, you stated as a requirement:
Using only BOI 4.1, I need to be able to subtract 2 dates to get a decimal number of days.
I am including a formula that will do the work for you on the report side. I have used this before… caveats listed after the formula.
Assume DATE1 and DATE2 are date objects that you need to subtract.
Warning: ugly code ahead!
=((( (ToNumber(SubStr(FormatDate( ,“HH:MM:SS”) ,1 ,2))6060) + (ToNumber(SubStr(FormatDate( ,“HH:MM:SS”), 4, 2))* 60) + ToNumber(SubStr(FormatDate( ,“HH:MM:SS”), 7, 2))) - ( (ToNumber(SubStr(FormatDate( ,“HH:MM:SS”) ,1 ,2))6060) + (ToNumber(SubStr(FormatDate( ,“HH:MM:SS”), 4, 2))* 60) + ToNumber(SubStr(FormatDate( ,“HH:MM:SS”), 7, 2)))) / 3600) + (DaysBetween( ,)*24)
Caveat 1: This formula assumes that no date value will ever be null.
Caveat 2: There is a known performance degradation when using the FormatDate() or FormatNumber() function in a BusinessObjects report.
Caveat 3: No provision is made for ignoring weekends, but then again, you would have problems doing that with database functions as well. Some people would like to see the difference between noon on Friday and noon on the following Monday as 1 day (24 hours). Hopefully this is not an issue for you!
Pseudo code for formula:
-
Translate time portion of date to a character string with FormatDate()
-
Use SubStr() to strip out the time information as Hours, Minutes, and Seconds
-
Use ToNumber() to translate each component back to a number
-
Multiply by an appropriate factor to translate each time component into seconds
-
Subtract the two numbers
-
Translate result back into hours by dividing by an appropriate factor
-
Add the number of hours per day obtained from DaysBetween() * 24
The net result is the difference between two dates as a decimal number of hours. To obtain days, simply divide the formula result I have provided by 24. Some sample results are shown below, where DATE1 and DATE2 are date objects and the last column is the result of the included formula. Rather than take up bandwidth to do a full explanation of the formula, I will leave that as an exercise for the reader. If you have any problems, feel free to drop me an email directly or post a follow-up to the list.
Note: if you copy and paste the info below into notepad then the spacing should be correct. Proportional fonts in email make formatting a challenge sometimes…
DATE1 DATE2 Time Diff
06/16/98 1:36:39 AM 06/17/98 3:31:51 PM 37.92
06/16/98 2:14:05 PM 06/17/98 2:14:05 AM 12.00
06/16/98 2:14:33 PM 06/16/98 8:14:33 PM 6.00
06/17/98 2:07:58 AM 06/17/98 1:39:10 PM 11.52
06/17/98 6:24:50 AM 06/18/98 1:22:26 PM 30.96
06/17/98 12:38:47 PM 06/17/98 1:50:47 PM 1.20
06/17/98 1:51:13 PM 06/18/98 1:51:13 AM 12.00
Listserv Archives (BOB member since 2002-06-25)