Milliseconds Calculation

[system]

Hi Team
I did not find my luck by searching in the forum.
Would anybody please guide me to get this solved in BOXI 3.1 SP 2 WebI report level, not in universe level.
DB Sybase 15
Task Started Date at: Dec 4, 2010 03:30:22
Task End Date at: Dec 4, 2010 13:10:10
I what milliseconds difference between Task Stared Date and Task End Date.
How to get this achieved.

---

amarnatha (BOB member since 2006-05-08)

[system]

Hi,

Calculate the difference of both dates in seconds and then multiply the result by 1000.

Your timestamps/dates do no look they have milliseconds. So all what you can get is the difference in seconds.

---

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

[system]

Marek Chladny

Thanks for your prompt replay . When I apply “DaysBetween” function and getting “0” (even after changing the format of the cell into decimals).
What is the function that I need to calculate?
Please do the needful

Thanks
Amar

---

amarnatha (BOB member since 2006-05-08)

[system]

@amarnatha

When I apply “DaysBetween” function and getting “0”

Yes, that’s expected because the function returns the whole number of days between 2 dates.

Here is a formula (for DeskI though so you need to re-write it for WebI) that can be used to calculate the difference in seconds between 2 dates:

Diff between two dates

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)

---

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

[system]

Hi Marek Chladny

I tried with given solution by you, but no luck .
Do please correct me where I am going wrong. Please find the attached screen shots of both DB and WebI

Date 1:
=DaysBetween([Start Date];[End Date])*86400 + (ToNumber(FormatDate([End Date];“HH”))*3600+ToNumber(Left(FormatDate([End Date];“mm:ss”);2))*3600 + ToNumber(FormatDate([End Date];“ss”)))

Date 2:
=(ToNumber(FormatDate([Start Date];“HH”))*3600+ToNumber(Left(FormatDate([Start Date];“mm:ss”);2))*3600 + ToNumber(FormatDate([Start Date];“ss”)))

Date 3: Date 1 - Date 2

The diffrence is not matching with the result set of DB.
Doc2.doc (72.0 KB)

---

amarnatha (BOB member since 2006-05-08)

[system]

You can give a try to below approach.

1. Convert date into string.
2. Using Substr() get the hour, min, sec separately for both dates.
3. Change H1, H2, M1, M2, S1, S2 into number.
4. Get the hour, min and sec difference and then change everything into miliseconds.

Hope this helps.

---

Rakesh_K (BOB member since 2007-12-11)

[system]

Hi Amarnath,

1. Check whether the dates that you are pulling in the report returns the format with Milli secs or only the date format.

If only the dates are returned even though you apply any logic at the report level to calculate the milli seconds will not work

2. If the dates are returned as expected then apply the logic to calculate the difference in mill seconds by multiplying with respective numbers.

3. Otherwise I would prefer to change the date to character in universe to in the date time format and then start using that in the report to calculate the difference in Milli Seconds format

Regards,
Kiran

---

bhkiran (BOB member since 2009-08-05)