BusinessObjects Board

Subtract 18 hours from Current Date

building reports webintelligence
#1

I want to subtract 18 hours from the current date… Is there a function that can subtract hours from the currentdate() function?

I tried relativedate() function but it can only do days…

thanks!

outatime (BOB member since 2007-11-20)

#2

aniketp :uk: (BOB member since 2007-10-05)

#3

Moderator note:

Aniket,

Please do not provide a “solution” where an OP is forwarded to another site where a solution is available only to premium members, i.e. is not free and needs to be paid for. This goes against the nature of this board.

Thanks for understanding.

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

#4

Hi Marek,

Thanks for letting me know and sorry I was not aware that it is paid site.
I saw the solution that’s why I shared, I don’t know what went wrong but sure from next time onwards I will take care of this…

DATEADD('n',18,DATETIMEFIELD)

aniketp :uk: (BOB member since 2007-10-05)

#5

That’s correct.

Try having a look at this old post in the DeskI forum. The formula posted there should get you started: Changing the time portion of timestamp
The final formula is not very clean and elegant so I suggest doing the calculation on the universe/SQL level, like Aniket suggested in his post above.

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

#6

I need to do it in the Webi layer because the custom sql access is disabled by the BO admins…

I’m trying to understand the code you have in Deski. Which part here should I insert the subtraction of 18 hours?

=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")

outatime (BOB member since 2007-11-20)

#7

Hi,

If I remember correctly (it’ been more than 5 years since I wrote that formula), the formula adds 1 hour to the date specified in . So all occurrences of “+ 3600” in the formula is about adding that many seconds, it means 1 hour to the given date.

So try changing all 4 occurrences of “+ 3600” to “- 64800”, which means subtracting of 64800 seconds, it means 18 hours.

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

#8

Hi All,
just checked, RelativeDate works with fractions, so to add 18 hours just do [finaldate] = RelativeDate([your date];0.75
Cheers,
Rogerio

rgoulart :brazil: (BOB member since 2011-08-21)

#9

That’s correct as well :slight_smile:

But it hasn’t been like this in all BOBJ versions. I think BO BI 4 introduced this enhancement to the RelativeDate() function.

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

#10

whoah! I never thought of doing that! you’re smart! :smiley:

thanks this will be useful for older BO versions! :+1:

outatime (BOB member since 2007-11-20)

#11

It wasn´t my idea, I took it from


Cheers,
Rogerio

rgoulart :brazil: (BOB member since 2011-08-21)