BusinessObjects Board

difference between time stamp

building reports desktopintelligence
#1

HI

I need to take out difference between time stamp…
in hrs and mins… Couldnt find anything…
lookingfor some desperate help…

Thanks

Neeraj

nupreti :india: (BOB member since 2005-04-28)

#2

Could you be more specific

NEO

KhoushikTTT :us: (BOB member since 2005-02-24)

#3

I have lot of object in the universe which store time stamp
such as received time, paid time, shipped time…

I want to calculate the difference between these time stamps…
so if received time was 9:30 AM and shipped time 1:42 PM
the difference should be between 4hrs 12 mins

nupreti :india: (BOB member since 2005-04-28)

#4

try this link,

To be more specific on the formula to seperate hours minutes and seconds, this is what is discussed in this post,

If that’s not an option, you can still convert the date/time to a numeric value and do your math from there. For example, starting with two dates “NowDate” and “TestDate”, you’d convert them to values as so:

NowValue=DaysBetween(ToDate("01/01/1900 00:00:00" ,"mm/dd/yyyy HH:mm:ss") ,<NowDate>) + (ToNumber(FormatDate(<NowDate> ,"HH"))/24) + (ToNumber(Right(FormatDate(<NowDate> ,"HHmm") ,2) ) /1440) + (ToNumber(FormatDate(<NowDate> ,"ss"))/86400) 
TestValue=DaysBetween(ToDate("01/01/1900 00:00:00" ,"mm/dd/yyyy HH:mm:ss") ,<TestDate>) + (ToNumber(FormatDate(<TestDate> ,"HH"))/24) + (ToNumber(Right(FormatDate(<TestDate> ,"HHmm") ,2) ) /1440) + (ToNumber(FormatDate(<TestDate> ,"ss"))/86400)

KhoushikTTT :us: (BOB member since 2005-02-24)

#5

HI

Neo I am getting an error when I am implementing the code that you have enclosed. The prob might be because the field I have is a time stamp and not a date field.
I also have a corresponding date field that I tried concatenating with the time stamp but that didnt work

Please advise…

Thanks

Neeraj

nupreti :india: (BOB member since 2005-04-28)

#6

If it’s an Oracle timestamp you may need to change it to a date. You can do this by

cast(timestampfield as date)

That is, if you don’t care about sub-second values. If you do, you’ll have to do something like

to_char(timestampfield, 'DD/MM/YYYY hh24:mi:ss:ff6'

within Oracle (the ff6 will give the subsecond value to 6dp)

Cheers
Scruffy

scruffy :australia: (BOB member since 2005-01-19)

#7

Greetings,

I am trying to use the formula NEO has provided in this thread and I am getting some wierd values. I was wondering if someone could tell me what I am doing wrong.

I have two data/time fields which are calculated from this:


=Min(<Activ Create DtTm Local(Activity IN)>)


=Min(<Activ Create DtTm Local(Activity Out)>)

I am then using this per NEO’s suggestion


=DaysBetween(ToDate("01/01/1900 00:00:00" ,"mm/dd/yyyy HH:mm:ss") ,<Min Activ In Dt>) + (ToNumber(FormatDate(<Min Activ In Dt> ,"HH"))/24) + (ToNumber(Right(FormatDate(<Min Activ In Dt> ,"HHmm") ,2))/1440) + (ToNumber(FormatDate(<Min Activ In Dt> ,"ss"))/86400)


=DaysBetween(ToDate("01/01/1900 00:00:00" ,"mm/dd/yyyy HH:mm:ss") ,<Min Activ Out Dt>) + (ToNumber(FormatDate(<Min Activ Out Dt> ,"HH"))/24) + (ToNumber(Right(FormatDate(<Min Activ Out Dt> ,"HHmm") ,2))/1440) + (ToNumber(FormatDate(<Min Activ Out Dt> ,"ss"))/86400)

Each formula is returning
“01/01/0000 12:00:00” as the value. Do you know what I need to change in order to get the difference in time from the two date/time fields?

Thanks,
J

jsolo (BOB member since 2004-03-31)