0

I am trying to convert Julian date 116175 to calendar date in MM/DD/YYYY format in OBIEE 11g Analysis. Used below in Edit formula, but didn't work out.

cast("CycleCounts"."PJUPMJ" +1900000 as char)

Julian - 116175 Calendar Date - 6/23/2016

The result should really show as - 6/23/2016

Can anyone please help me?

Thanks!

mikki
  • 1
  • So 'JDE Julian' not normal Julian; the first three digits are the year offset from 1900, and the last three digits are the day of the year? ([Or CYYDDD](http://www.kirix.com/stratablog/jd-edwards-date-conversions-cyyddd)). Are you looking for something that will run in the database, or only in the OBIEE layer? – Alex Poole Jun 24 '16 at 23:20
  • I would like to convert at either OBIEE analysis or at rpd level – mikki Jun 24 '16 at 23:38
  • Anyone who can help me on this please ? – mikki Jul 07 '16 at 15:57
  • I could do an Oracle SQL solution but not familiar with OBIEE or rpd, sorry. – Alex Poole Jul 07 '16 at 16:05

1 Answers1

0
Select Decode (Nvl (119031, 0), 0, To_Date (Null),To_Date (To_Char (119031 + 1900000) , 'YYYYDDD')) From Dual

where 119031 is your date value

Pika Supports Ukraine
  • 3,612
  • 10
  • 26
  • 42
Eoin2211
  • 911
  • 2
  • 19
  • 39