Author Topic: Brio/Hyperion - DATEADD Syntax for seconds past epoch (01/01/1970)  (Read 5129 times)

tony

  • Guest
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! :)

Marked as best answer by on June 22, 2019, 02:47:59 AM

Offline borkokrz

  • Full Member
  • ***
  • Posts: 141
  • Karma: 5
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

  • Guest
Re: Brio/Hyperion - DATEADD Syntax for seconds past epoch (01/01/1970)
« Reply #2 on: November 16, 2009, 09:55:30 PM »
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!

:(


Offline borkokrz

  • Full Member
  • ***
  • Posts: 141
  • Karma: 5
Re: Brio/Hyperion - DATEADD Syntax for seconds past epoch (01/01/1970)
« Reply #3 on: November 16, 2009, 10:03:44 PM »
Strange, it os working fine in my environment. Check attached bqy.

tony

  • Guest
Re: Brio/Hyperion - DATEADD Syntax for seconds past epoch (01/01/1970)
« Reply #4 on: November 16, 2009, 10:12:13 PM »
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

Offline borkokrz

  • Full Member
  • ***
  • Posts: 141
  • Karma: 5
Re: Brio/Hyperion - DATEADD Syntax for seconds past epoch (01/01/1970)
« Reply #5 on: November 16, 2009, 10:19:12 PM »
you are welcome :-)