system
August 14, 2009, 5:46pm
#4
Hi,
Definitely not an elegant formula, but it should work
=ToDate(FormatDate(RelativeDate(ToDate(FormatDate(<date variable> ,"dd-mm-yyyy") ,"dd-mm-yyyy") ,Floor((ToNumber(FormatDate(<date variable> ,"HH"))*3600 + ToNumber(Right(FormatDate(<date variable> ,"HHmm") ,2))*60 + ToNumber(FormatDate(<date variable> ,"ss")) + 3600)/86400)) ,"dd-mm-yyyy") & " " & FormatNumber(Floor((ToNumber(FormatDate(<date variable> ,"HH"))*3600 + ToNumber(Right(FormatDate(<date variable> ,"HHmm") ,2))*60 + ToNumber(FormatDate(<date variable> ,"ss")) + 3600)/3600) ,"00") & ":" & FormatNumber(Floor(Mod((ToNumber(FormatDate(<date variable> ,"HH"))*3600 + ToNumber(Right(FormatDate(<date variable> ,"HHmm") ,2))*60 + ToNumber(FormatDate(<date variable> ,"ss")) + 3600) ,3600)/60) ,"00") & ":" & FormatNumber(Mod(Mod((ToNumber(FormatDate(<date variable> ,"HH"))*3600 + ToNumber(Right(FormatDate(<date variable> ,"HHmm") ,2))*60 + ToNumber(FormatDate(<date variable> ,"ss")) + 3600) ,3600) ,60) ,"00") , "dd-mm-yyyy HH:mm:ss")
It should also work for dates when adding 1 hour changes the date.
Marek Chladny (BOB member since 2003-11-27)
system
August 17, 2009, 8:09am
#5
Hi
Thanks Marek, it’ really complext formula, its working fine and giving me the result what i need. . Thanks again for your input.
Regards
Siva.M
looksmee (BOB member since 2006-02-08)
system
November 4, 2010, 4:52pm
#6
Marek Chladny:
Hi,
Definitely not an elegant formula, but it should work
=ToDate(FormatDate(RelativeDate(ToDate(FormatDate(<date variable> ,"dd-mm-yyyy") ,"dd-mm-yyyy") ,Floor((ToNumber(FormatDate(<date variable> ,"HH"))*3600 + ToNumber(Right(FormatDate(<date variable> ,"HHmm") ,2))*60 + ToNumber(FormatDate(<date variable> ,"ss")) + 3600)/86400)) ,"dd-mm-yyyy") & " " & FormatNumber(Floor((ToNumber(FormatDate(<date variable> ,"HH"))*3600 + ToNumber(Right(FormatDate(<date variable> ,"HHmm") ,2))*60 + ToNumber(FormatDate(<date variable> ,"ss")) + 3600)/3600) ,"00") & ":" & FormatNumber(Floor(Mod((ToNumber(FormatDate(<date variable> ,"HH"))*3600 + ToNumber(Right(FormatDate(<date variable> ,"HHmm") ,2))*60 + ToNumber(FormatDate(<date variable> ,"ss")) + 3600) ,3600)/60) ,"00") & ":" & FormatNumber(Mod(Mod((ToNumber(FormatDate(<date variable> ,"HH"))*3600 + ToNumber(Right(FormatDate(<date variable> ,"HHmm") ,2))*60 + ToNumber(FormatDate(<date variable> ,"ss")) + 3600) ,3600) ,60) ,"00") , "dd-mm-yyyy HH:mm:ss")
It should also work for dates when adding 1 hour changes the date.
Hi Marek
i’m using Teradata DB and i have a question
i have to find the difference between 2 date…
format is HH.mm.ss
for example
1 object 15:26:48
2 object 10:00:00
i aspect: 05:26:48
can you help me??
thanks Marek
Barticchia (BOB member since 2008-06-09)
system
November 4, 2010, 8:12pm
#7
Hi barticchia,
If this is a question how to do it in the database / universe then you should better ask the question in the Semantic Layer / Universe Designer forum and not here. Or are you doing the calculation in a DeskI/WebI report?
Marek Chladny (BOB member since 2003-11-27)
system
November 4, 2010, 9:30pm
#8
Hi marek
i’m trying to do it into the webi report… but i don’t know if i can…
what do you think about?
Barticchia (BOB member since 2008-06-09)
system
November 4, 2010, 9:43pm
#9
Hi,
Convert both times into number of seconds:
so for instance 15:26:48 will be 153600 + 26 60 + 48 = 55608 seconds
Then get the difference of both numbers - result will be in seconds.
Then use the formula in this old topic to convert the number of seconds back to HH:MM:SS format:
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
November 4, 2010, 10:02pm
#10
Marek Chladny:
Hi,
Convert both times into number of seconds:
so for instance 15:26:48 will be 153600 + 26 60 + 48 = 55608 seconds
Then get the difference of both numbers - result will be in seconds.
Then use the formula in this old topic to convert the number of seconds back to HH:MM:SS format:
Convert number of seconds to actual time
many thanks marek…tomorrow i’ll try
i have to use the “floor” function?
Barticchia (BOB member since 2008-06-09)
system
November 4, 2010, 10:06pm
#11
Is there anything wrong with the function?
Marek Chladny (BOB member since 2003-11-27)
system
November 4, 2010, 10:19pm
#12
hahahahhaha no no…just to know…
many many thanks marek
Barticchia (BOB member since 2008-06-09)
system
November 5, 2010, 6:23am
#13
Or just
RelativeDate( [Date col]; 1/24)
Mick
mikca (BOB member since 2005-12-16)
system
November 5, 2010, 7:20am
#14
Does the function work with fractions as well? Must be something new if it does.
Marek Chladny (BOB member since 2003-11-27)
system
November 5, 2010, 10:02am
#15
not new always has
mikca (BOB member since 2005-12-16)
system
November 5, 2010, 10:26am
#16
Not true in older BO versions
Marek Chladny (BOB member since 2003-11-27)
system
November 5, 2010, 11:01am
#17
Hi marek
firs of all i wanna say thank for your help…
so i tried to do what you said…and everythings works…except in some cases…when Dep is 00:00:00…
exemple
Arr= 05:30:00
Dep=00:00:00
Result=06:30:00 (1 hour more)
i aspect the difference is 05:30:00…
i used this function (i’m italian so i tried to traslate the functions in english to italian)
this is yours :
=FormatNumber(Floor(/3600) ,“00”) & “:” &
FormatNumber(Floor(Mod( ,3600)/60) ,“00”) & “:” &
FormatNumber(Mod(Mod( ,3600) ,60) ,“00”)
this is mine :
=FormatoNumero(Arrot([_Sottrazione Secondi]/3600;0);“00”) + “:” + FormatoNumero(Arrot(Resto([_Sottrazione Secondi];3600)/60;0);“00”) + “:” + FormatoNumero(Resto(Resto([_Sottrazione Secondi];3600);60);“00”)
Formatnumber=Formatonumero
Floor=Arrot (I’m not sure about this…couse this function Rounds a number to a specified number of decimal)
Mod=Resto
Barticchia (BOB member since 2008-06-09)
system
November 5, 2010, 11:48am
#18
Hi,
Floor() function is defined like this:
It’s not about rounding.
I am not sure what your Arrot() function does, though. :?
Marek Chladny (BOB member since 2003-11-27)
system
November 5, 2010, 12:35pm
#19
system
November 5, 2010, 2:01pm
#20
It’s good to know that there are still people that have fun when developing reports!
Marek Chladny (BOB member since 2003-11-27)
system
November 5, 2010, 2:05pm
#21
ahahha i really love it…even if sometimes i’d like break everything!!! hahahaha
Barticchia (BOB member since 2008-06-09)
system
July 5, 2011, 9:56am
#22
Hi Marek,
Can you give me the generic solution to add hours to my timestamp? In one case, I need to add 2 hours and in other case 3 hours. So help me with that? Your formula works great for adding 1 hour. I just could not add 2 or 3 hours.
mradhakr (BOB member since 2011-07-05)
system
July 5, 2011, 11:03am
#23
Welcome to B:bob:B !
Do you mean my long and complicated formula from Fri Aug 14, 2009 7:46 pm?
If yes then as you can find out, there are 4 occurrences of “+ 3600” substring in the whole formula. It represents the number of second in 1 hour (60 minutes x 60 seconds). If you change it to 7200 (which is 2 hours) or 10800 (which is 3 hours) then you will get what you need.
Marek Chladny (BOB member since 2003-11-27)