In a message dated 99-02-25 17:56:56 EST, you write:
The start and end date include Hours, minutes and seconds. For example:
1999-02-24 11:51:34
1999-02-24 17:51:50
Has anyone created an object that calculates the time difference between
two
date variables? Is it as simple as using MONTHS_BETWEEN(batch_start_date,
batch_end_date) and then multiplying by some factor?
I’ve tried MONTHS_BETWEEN, but I don’t seem to get any results.
Thanks.
Amy Gutmann
Hi, Amy! It’s been a while…
If you can, you should be able to do the math in the database just by
subtracting one date from the other. Oracle does nice date math, in that it
will include the time element automatically. In other words, with date 1 being
2/25/99 10:00 and date 2 being 2/25/99 22:00, date 2 - date 1 will result in
0.5, or one half of a day. You don’t need the months_between() or any other
Oracle function for this.
If, however, you need to do the date math on a BusinessObjects report, you can
use the following info. It’s ugly, but it works! I posted this formula a while
back. I saved it; after reading it, you will understand why I didn’t want to
have to create it again.
Regards,
Dave Rathbun
Integra Solutions
www.islink.com
In a message dated 98-06-18 01:41:00 EDT:
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)