cancel
Showing results for 
Search instead for 
Did you mean: 

How to calculat hour difference in WEBI (BOXI R2)

Former Member
0 Kudos

Hi,

How do I get the hour difference between two date?

I only can find this function DaysBetween which only returns the date diff in integer.

The '-' seems not working too when I try to difine the variable. (type not match error)

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi Pug, I had to get this done and actually it's a right pain this should be a simple function in webi like they have in Excel. Now I am short ontime at the moment, but here's the basics:

daysbetween() multipled by seconds in the day give avery rough estimategive or take 23 hrs 59 mins & seconds.

subtract the difference between the clock time in seconds  (Seconds_difference) you can ignore the day

where:

Seconds_difference = (EndtimeSeconds - StarttimeSeconds)

And:

EndtimeSeconds =

ToNumber(Substr(FormatDate([EndTime] ; "HH:mm:ss");1;2))*60*60

+ToNumber(Substr(FormatDate([EndTime];"HH:mm:ss");4;2))*60

+ToNumber(Substr(FormatDate([EndTime];"HH:mm:ss");7;2))

And:

StarttimeSeconds =

=ToNumber(Substr(FormatDate([StartTime] ; "HH:mm:ss");1;2))*60*60

+ToNumber(Substr(FormatDate([StartTime];"HH:mm:ss");4;2))*60

+ToNumber(Substr(FormatDate([StartTime];"HH:mm:ss");7;2))

And as they are on the same data no need for the daysbetween function. This will give you the time difference in seconds then you divide by 3600 and voila time difference in hours. its painful but nonetheless logical.

Former Member
0 Kudos

wow just spotted the dates sorry you had to wait 3+ yrs but thats BO for you

Former Member
0 Kudos

Hi

There are two ways how you can get the hour difference...

1. First a case like this you can use universe to take hour difference

like (datebase sql server ) : datediff(hh,startdate,endate )

2. we can use this function to get a hour difference between to date. =DaysBetween(CurrentDate();RelativeDate(CurrentDate();1)) * 24

so think which one you going to incorporate.

arif

Former Member
0 Kudos

Thanks for the reply. but my problem doesn't solve.

1. I am using Oracle. In Oracle, we can just simply use

(date1 - date2) * 24 to get the hour diff. But both BOXI webi and Designer seems not support the minus operation.

2. What I want is to get the real hour difference even the two dates are in the same date.

for example:

date1 = 23/10/2008 10:00

date2 = 23/10/2008 09:00

The hour diff is 1,

If I use DaysBetween function, I got 0, since date1 and date2 are in same day.

Edited by: Pugazhendhi V on Oct 23, 2008 11:05 AM