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!!!
Lisa Main
Listserv Archives (BOB member since 2002-06-25)
Lisa;
Check to see if your database offers this function. We have built many objects
in our universes to handle this. The Sybase command is “datediff”.
Regards
Bruce Hinrichs
Schneider National, Inc.
Green Bay, WI USA
HinrichsB@Schneider.Com
Listserv Archives (BOB member since 2002-06-25)
Lisa,
If your fields are defined with date data types, you should be able to just subtract them. The following is an example I used to create an “age” object that calculates a person’s approximate age in BO 4.1.
(TODAY - @Select(birth date))/365.25
FYI, we’re using Informix as the underlying DBMS, but this should work on any DBMS with a date data type. You may also need to change the object’s format to have decimals appear on your report.
====================================================================== Dennis Edgecombe e-mail: dennis_edgecombe@wsu.edu
Senior Data Analyst phone: (509)335-7062
Washington State University fax: (509)335-6635 ext. 5-7062
Information Technology homepage: http://www.wsu.edu/~edgecom
PO Box 641222
Pullman, WA 99164-1222
======================================================================
Listserv Archives (BOB member since 2002-06-25)
If you are using Oracle, you just have to subtract the two dates mathematically (Date2 - Date1). You can’t do this in a formula or variable, but you can do this in a User Object.
Erich Hurst
Compaq Computer Corporation
(281) 514-9445
Erich.Hurst@Compaq.com
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!!!
Listserv Archives (BOB member since 2002-06-25)
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(<DATE2> ,"HH:MM:SS") ,1 ,2))*60*60) + (ToNumber(SubStr(FormatDate(<DATE2> ,"HH:MM:SS"), 4, 2))* 60) + ToNumber(SubStr(FormatDate(<DATE2> ,"HH:MM:SS"), 7, 2))) - ( (ToNumber(SubStr(FormatDate(<DATE1> ,"HH:MM:SS") ,1 ,2))*60*60) + (ToNumber(SubStr(FormatDate(<DATE1> ,"HH:MM:SS"), 4, 2))* 60) + ToNumber(SubStr(FormatDate(<DATE1> ,"HH:MM:SS"), 7, 2)))) / 3600) + (DaysBetween(<DATE1> ,<DATE2>)*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.00Regards,
Dave Rathbun
Integra Solutions
www.islink.com See you in Orlando in '98!
Listserv Archives (BOB member since 2002-06-25)
What do we need to do if we want the diffrnce in HH:MIN:SEC
sknybatra (BOB member since 2010-10-20)
Objects start and end are datetime. This works in SQL Server:
SELECT cast(DateDiff(hh, start, end) % 24 as varchar) + ':' + cast(DateDiff(mi, start, end) % 60 as varchar) + ':' + cast(DateDiff(ss, start, end) % 60 as varchar)charlie 
(BOB member since 2002-08-20)