Changing the time portion of timestamp

building reports desktopintelligence
1

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 :uk: (BOB member since 2006-02-08)

2

If it’s Oracle, you should be able to do datecol+(1/24)

Ignore that, just seen that you want to do it at report level, sorry.

=CurrentDate() + 1/24 should work

3

Hi

Thanks Mark for your input, i was tried this and its not giving the current time +one hour. It reamin show’s the current time only.

Regards
Siva.M

looksmee :uk: (BOB member since 2006-02-08)

4

Hi,

Definitely not an elegant formula, but it should work :slight_smile:

=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") &amp; " " &amp; FormatNumber(Floor((ToNumber(FormatDate(<date variable> ,"HH"))*3600 + ToNumber(Right(FormatDate(<date variable> ,"HHmm") ,2))*60 + ToNumber(FormatDate(<date variable> ,"ss")) + 3600)/3600) ,"00") &amp; ":" &amp; 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") &amp; ":" &amp; 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 :slovakia: (BOB member since 2003-11-27)

5

Hi

Thanks Marek, it’ really complext formula, its working fine and giving me the result what i need. :smiley: . Thanks again for your input.

Regards
Siva.M

looksmee :uk: (BOB member since 2006-02-08)

6

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 :mrgreen: :hb: :hb: :hb: :hb:

Barticchia :it: (BOB member since 2008-06-09)

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 :slovakia: (BOB member since 2003-11-27)

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 :it: (BOB member since 2008-06-09)

9

Hi,

Convert both times into number of seconds:
so for instance 15:26:48 will be 153600 + 2660 + 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:

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

10

many thanks marek…tomorrow i’ll try :smiley:
i have to use the “floor” function?

Barticchia :it: (BOB member since 2008-06-09)

11

Is there anything wrong with the function? :shock: :slight_smile:

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

12

hahahahhaha no no…just to know… :stuck_out_tongue:

many many thanks marek

Barticchia :it: (BOB member since 2008-06-09)

13

Or just
RelativeDate( [Date col]; 1/24)

Mick

mikca :australia: (BOB member since 2005-12-16)

14

Does the function work with fractions as well? Must be something new if it does.

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

15

not new always has

mikca :australia: (BOB member since 2005-12-16)

16

Not true in older BO versions :nonod:

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

17

Hi marek

firs of all i wanna say thank for your help… :slight_smile:

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 :hb: (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

:hb: :hb: :hb: :hb: :hb:

Barticchia :it: (BOB member since 2008-06-09)

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 :slovakia: (BOB member since 2003-11-27)

19

I DID IT!!! HAHAHHAHAHHAHAHAHAHHAHA
:mrgreen: :mrgreen: :mrgreen: :mrgreen: :mrgreen: :mrgreen: :mrgreen: :mrgreen: :mrgreen: :mrgreen: :mrgreen: :mrgreen:

THe function,wasn’t Arrot, but InteroInf
hahahah i know for you doesn’t mean nothing…hahah but in italian language, floor is InteroInf!!!

Really Really really thanks Marek!!!

I post here what i’ve wrote into the function (might be useful to someone) :wink:

=FormatoNumero(InteroInf([_Sottrazione Secondi]/3600);“00”) + “:” + FormatoNumero(InteroInf(Resto([_Sottrazione Secondi];3600)/60);“00”) + “:” + FormatoNumero(Resto(Resto([_Sottrazione Secondi];3600);60);“00”)

Barticchia :it: (BOB member since 2008-06-09)

20

It’s good to know that there are still people that have fun when developing reports! :rotf:

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