Hello All,
I’m trying to get the resolution time between two date columns in Oracle 8.0.5 :
for each record i have the fields END_DATE and START_DATE.
I can I do it in BO ?
Do I need to create a view, function, ??? does anyone know a fast solution ?
Listserv Archives (BOB member since 2002-06-25)
In a message dated 00-06-01 08:36:35 EDT, you write:
I’m trying to get the resolution time between two date columns in Oracle 8.0.5 :
for each record i have the fields END_DATE and START_DATE.
I can I do it in BO ?
Do I need to create a view, function, ??? does anyone know a fast solution
?
Do you have access to the universe design? If so, simply subtract the two dates i.e. END_DATE - START_DATE. The resulting object will be a numeric value, where the integer portion will be the number of days and the decimal portion is the fraction of a day. In other words, 1.5 means 1 1/2 days or a total of 36 hours.
If you do not have access to the universe design then you are going to have to subtract the two dates in the client instead. The problem here is that while BusObj provides the DaysBetween() function, it does not process times. So instead of 1.5 days (from the prior example) you would only get 1.0.
If you really need to get the time, the solution I have come up with is to convert the date value to a string, substring all of the different parts of the time, convert everything back to numeric values, convert everything to a common factor (seconds, minutes, hours… all become seconds), and then do the subtraction. Then convert everything back to a unit of choice.
An ugly formula follows… replace DATE1 and DATE2 as appropriate:
=((( (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)
Have fun…
Regards,
Dave Rathbun
Integra Solutions
www.islink.com
Listserv Archives (BOB member since 2002-06-25)
You Can Use
DaysBetween(date1, date2)
Description Returns the number of days between two dates.
Example DaysBetween(‘10-04-94’,‘12-04-94’) returns 2.
Hello All,
I’m trying to get the resolution time between two date columns in Oracle 8.0.5 :
for each record i have the fields END_DATE and START_DATE.
I can I do it in BO ?
Do I need to create a view, function, ??? does anyone know a fast solution ?
Listserv Archives (BOB member since 2002-06-25)
Are you kidding me??? There has GOT to be an easier way!! UGH!! This is exactly what I have to do…in the report…anyone have a simpler solution?? 
Refering to the solution including time, not just using daysbetween
Tammy 
(BOB member since 2002-11-12)
Tammy,
Nope. Dave’s solution is the only way I know of. But you can take his formula and paste it into a variable, and just replace his objects with your own, and it should work fine.
MichaelWelter 
(BOB member since 2002-08-08)
Thank goodness for people like Dave!!
Ok, here’s what’s happening for me…
I have two data providers each pointed to a different universe, one going against Oracle, one against SQLServer.
I have the two data providers linked on license number, and each data provider is also returning a date (one is an order date, one is a backorder date). Because of the whole problem with dimensions coming from two different dataproviders, I have created a detail object for each of the dates.
When I try to create a variable using Daves formula (populating date1 with the detail object from query1 and date2 with the detail object from query2), I get an error Variables not Compatible.
I then tried to create a detail variable using Daves formula, referencing not the detail objects, but the actual dimensions and got the same result.
Do I have to apply a formula to the individual detail date objects prior to using them in Daves variable???
YIKES!!
Tammy (BOB member since 2002-11-12)
No, I don’t think so. Did you make sure that your variable is set as a numeric data type?
MichaelWelter (BOB member since 2002-08-08)
Ok, was able to get the variable created (thanks for the tip on data type), and now am getting a computation error as it appears that I have a linking problem that I must resolve first.
The DP’s are linked on License Number so when I just pull the vanilla data into the report I would expect to get one row per license number. I would expect the row to include the detail date object from query 1 and the detail date object from query 2 (see below).
Licencee#-----Order Date (Qry1)-----Backorder Date (Qry2)
1234567-------5/6/2003 12:03:15-----5/7/2003 15:45:30
Instead, I’m getting two rows back for each license number (see below - where EMPTY it’s actually empty, nothing, null).
License #-----Order Date (Qry1)-----Backorder Date (Qry2)
1234567------5/6/2003 12:03:15----EMPTY
1234567------EMPTY--------------------5/7/2003 15:45:30
I’ve linked DP’s many times in the past and have never had this happen. Any suggestions???
Thanks so much for your help!!
Tammy (BOB member since 2002-11-12)
Check your data providers and make sure they are linked ONLY on License #. They may be linked on another object that doesn’t have one value per license.
MichaelWelter (BOB member since 2002-08-08)
Yep, did that. I have had that problem in the past. They are ONLY linked on License #?? :? sigh.
Tammy (BOB member since 2002-11-12)
OK, one more idea. In your table, display the License number from the other data provider. In other words, if you are currently using License # from DP1, then change it to use License # from DP2. See if that helps.
I’m running out of ideas. 
MichaelWelter (BOB member since 2002-08-08)
Hmmmm, that didn’t work either. I also tried applying a filter to display only those license numbers that exist in the ‘driver’ query…alas, no change.
How odd…perhaps it has something to do with the differing databases (grasping at straws here).
Well, thanks for your help…if you come up with anything else, please let me know.
Thanks.
Tammy (BOB member since 2002-11-12)
Please, do a search on BOB, keywords: link data provider Bickerton
Could it be that your Licence number has trailing blanks?
Andreas 
(BOB member since 2002-06-20)
Funny you should mention that…I just happened to think of that this morning in the shower (wish I could leave this stuff at work). We’re going to give it a try…I expect a trim to be the solution after which we will be able to apply the earlier mentioned formula.
Thanks so much for all your help!! 
Tammy (BOB member since 2002-11-12)