Diff between two dates

[system]

I am trying to create a variable in Desktop Intelligence XI that will give me the diff between two dates. I used “=DaysBetween(<Admit Date & Time> ,<Order Date & Time>)” and I get just days. Is there a way to get his broke down to hours, minutes and seconds?

---

djohnson (BOB member since 2007-07-26)

[system]

Unfortunately no, DaysBetween() function gives the result only in days.

A possible workaround would be to convert both dates to the numbers of seconds from some date&time in past (for instance January 1 2008 00:00:00) then do the difference of these 2 numbers (to get the difference between date1 and date2 in seconds) and then convert the result back to hh:mi:ss for instance.

---

Marek Chladny (BOB member since 2003-11-27)

[system]

I am some what new to writing variables (no official trainng). Here is a variable I created but my numbers didnt look right…

=(((DaysBetween(<Admit Date & Time> ,<Order Date & Time>))*1440)+(ToNumber(SubStr(FormatDate(<Order Date & Time> ,“HH:mm:ss”) ,1 ,2)) -

ToNumber(SubStr(FormatDate(<Admit Date & Time> ,“HH:mm:ss”) ,1 ,2)))*48 +(ToNumber(SubStr(FormatDate(<Order Date & Time> ,“HH:mm:ss”) ,4 ,2)) -

ToNumber(SubStr(FormatDate(<Admit Date & Time> ,“HH:mm:ss”) , 4 , 2))))/60

I appreciate your help with this.

---

djohnson (BOB member since 2007-07-26)

[system]

Hi,

Try the following:

1. Create a report-level variable that calculates the number of seconds between 2 dates:

=DaysBetween(<ADMIT_DATE> ,<ORDER_DATE>) * 86400
 +
 (
   ToNumber(FormatDate(<ORDER_DATE> ,"HH")) * 3600 + 
   ToNumber(Left(FormatDate(<ORDER_DATE> ,"mm:ss") ,2)) * 60 + 
   ToNumber(FormatDate(<ORDER_DATE> ,"ss"))
 )
 -
 (
   ToNumber(FormatDate(<ADMIT_DATE> ,"HH")) * 3600 + 
   ToNumber(Left(FormatDate(<ADMIT_DATE> ,"mm:ss") ,2)) * 60 + 
   ToNumber(FormatDate(<ADMIT_DATE> ,"ss"))
 )

2. Then use this formula to format the variable:

=FormatNumber(Floor(<number of seconds>/86400) ,"0") &amp; " day(s) " &amp;
FormatNumber(Floor(Mod(<number of seconds> ,86400)/3600) ,"00") &amp; ":" &amp;
FormatNumber(Floor(Mod(Mod(<number of seconds> ,86400) ,3600)/60) ,"00") &amp; ":" &amp;
FormatNumber(Mod(Mod(Mod(<number of seconds> ,86400) ,3600) ,60) ,"00")

Did it work?

---

Marek Chladny (BOB member since 2003-11-27)

[system]

I tried the variable and it did not work. I get the error not enough parameters.

I noticed my commands are Admit Date and time and Order Date and Time. So I changed it and I get another error. Incorrect Data type DMB0003. When I look at the variable it has the + highlighted. I will copy upto where it is erroring out.

=DaysBetween(<Admit Date & Time> ,<Order Date & Time>) * 86400
+
( ToNumber(FormatDate(<Order Date & Time> ,“HH”)) * 3600 +
ToNumber(Left(FormatDate(<Order Date & Time> ,“mm:ss”) ,2) * 60 + (here).

---

djohnson (BOB member since 2007-07-26)

[system]

Then try this one:

=DaysBetween(<Admit Date &amp; Time> ,<Order Date &amp; Time>) * 86400 + ( ToNumber(FormatDate(<Order Date &amp; Time> ,"HH")) * 3600 + ToNumber(Left(FormatDate(<Order Date &amp; Time> ,"mm:ss") ,2)) * 60 + ToNumber(FormatDate(<Order Date &amp; Time> ,"ss"))) - (ToNumber(FormatDate(<Admit Date &amp; Time> ,"HH")) * 3600 + ToNumber(Left(FormatDate(<Admit Date &amp; Time> ,"mm:ss") ,2)) * 60 + ToNumber(FormatDate(<Admit Date &amp; Time> ,"ss")))

It is in fact the same formula but written in 1 line instead of more. I used more lines above for the formula be more readable.

---

Marek Chladny (BOB member since 2003-11-27)

[system]

I hate to give good news and bad news in the same reply but… first thank you for your help.

It works great and all looks good. When I created the second variable i get an error message. "Not enough paramerters DMB0006 My curser starts flickering here

=FormatNumber(Floor(

---

djohnson (BOB member since 2007-07-26)

[system]

How about the same without carriage returns (no extra “enters”):

=FormatNumber(Floor(<number of seconds>/86400) ,"0") &amp; " day(s) " &amp; FormatNumber(Floor(Mod(<number of seconds> ,86400)/3600) ,"00") &amp; ":" &amp; FormatNumber(Floor(Mod(Mod(<number of seconds> ,86400) ,3600)/60) ,"00") &amp; ":" &amp; FormatNumber(Mod(Mod(Mod(<number of seconds> ,86400) ,3600) ,60) ,"00")

Did you give the first variable the name “number of seconds”? If you used any other name then you need to change the above formula accordingly.

---

Marek Chladny (BOB member since 2003-11-27)

[system]

OMG… You are the greatest! Thank you for all your help… If you havnt figured it out yet… it worked.

---

djohnson (BOB member since 2007-07-26)

[system]

@djohnson

OMG… You are the greatest! Thank you for all your help… If you havnt figured it out yet… it worked.

Perfect 8) I am glad it helped.

---

Marek Chladny (BOB member since 2003-11-27)

[system]

Hi

This has been fantastic advice and I have managed to recreate it for my use as specified. I have one question that puts a slightly different slant on it in that I would like to display my elapsed time in hours, minutes and seconds. Therefore if it goes over 24 hours, I’d still like my results to show something like 27:16:23. I don’t really understand Mod so any pointers would be really appreciated.

---

jmmorton (BOB member since 2004-07-05)

[system]

Hi Jonno,

Then try the formula from this post:

Convert number of seconds to actual time

We have a value in our database that is the number of seconds past midnight. Is there a way to convert this to hh:mm:ss in Business Objects (hh:mm would suffice) Many thanks in anticipation caz (BOB member since 2007-04-11)

---

Marek Chladny (BOB member since 2003-11-27)

[system]

That’s fantastic - worked a treat. Thanks ever so much Marek.

---

jmmorton (BOB member since 2004-07-05)

[system]

Hi Mark,

I have similar problem. I tried your solution but it didn’t worked please help me.

I have 2 dates

1. Submit date (dd/mm/yyyy hh:mm:ss)
2. Responded date (dd/mm/yyyy hh:mm:ss)

User wants to know the response time in HH:MM:SS

the date in the data base is entered in UTC and we are using a custom function in oracle to extract date from that UTC in to BO based on time zone.

Help me with this as this is really imp…

Thanks in advance,

SAM

---

sam4u (BOB member since 2008-01-09)

[system]

@sam4u

I tried your solution but it didn’t worked

Hi,
when you said that you should have also said what the problem was, how it behaved. Did you get any error message or wrong results or…?

---

Marek Chladny (BOB member since 2003-11-27)

[system]

Hi Mark,

There was some syntax error ( missing paranthesis) i fixed it an finally it worked. Thanks for your response.

---

sam4u (BOB member since 2008-01-09)

[system]

This is a very interesting article, many thanks for posting it however, is there an easy way to add a time field to a date field to give a date time field?

Regards

Paul

---

figdatbong (BOB member since 2008-03-27)

[system]

Hi,

@figdatbong

is there an easy way to add a time field to a date field to give a date time field?

This topic may help:

Changing the time portion of timestamp

Hi I just wanted to add an hour with my existing timestamp, your input is more appreciated. I wanted to do this only on the Report and not on the universe. Say the time in the column 14-08-2009 14:30:00, i wanted to display it as 14-08-2009 15:30:00 Regards Siva.M looksmee (BOB member since 2006-02-08)

---

Marek Chladny (BOB member since 2003-11-27)

[system]

Hi Marek

I would like to thank you for submitting this and other related posts. Using them as a guide, I have been able to develop a solution that works i.e. determine whether contractors are meeting specified targets.

Many thanks

Regards

Paul

---

figdatbong (BOB member since 2008-03-27)

[system]

Hi Marek,

The solution with the formulae to the time difference worked out well.

You are brilliant. Thank you.

---

bobuser9 (BOB member since 2007-04-20)