Brio/Hyperion - DATEADD Syntax for seconds past epoch (01/01/1970)

[tony]

Hi everyone!

I've been going around in circles trying to get the right syntax to add seconds to the epoch date (01/01/1970).  I have a field (from the LOGIN table) which contains a TIME key, which is seconds past the epoch date.  I've ensured that the digits are converted to a number and I've tried about 15 different formats to get the number converted to a simple date format, by using DATEADD.  Depending on where you look, the format is slightly different (java, vb, vba, sql, plsql, sqr, etc...) but none of them seem to work in Brio/Hyperion, against an Oracle database.  I'm adding a Computed Item in my Query Results, using a converted version of the TIME digits but all I get is (hash)NAME! as a result, no matter what I try... it's really annoying because this (I know!) is very very simple...

Does anyone have a working model/syntax for DATEADD, using seconds past 01/01/1970 for Brio/Hyperion running against Oracle 9i???

Thanks!

[borkokrz]

On Oracle try this as computed item:

to_date('1970-01-01','yyyy-mm-dd')+(Login.Time+7200)/86400

with DataType=Date in Brio.

7200 is for DST, change to 3600 for winter time.

[tony]

Sorry, no - that didn't work... I can work with GMT so won't be needing a dateline offset...

I tried to_date('1/1/70 00:00 AM', 'm/d/yy HH:MM AM')+(Computed/86400)

where Computed = LOGIN.Time and Option = Data Type = Date (m/d/yy HH:MM AM)

Still get hashName!

[borkokrz]

Strange, it os working fine in my environment. Check attached bqy.

[tony]

Ahhh... I never thought to put it in the Query/Request - I was trying to apply it in the Results... doh!

That just goes to show that you can look at something for too long and lose sight of what you wanted in the first place...

Thanks very much - works now..

T

[borkokrz]

you are welcome :-)