Cognos Analytics

Cognos Analytics

Connect, learn, and share with thousands of IBM Cognos Analytics users! 

 View Only

  • 1.  How to Convert Julian Date

    Posted Tue January 21, 2020 12:27 PM
    ​Hello,

    Can someone assist with details on how to convert a julian date to a month/day/year format?  We have an Oracle DB.
    Thank you,
    jen

    ------------------------------
    Jenifer Broughton
    ------------------------------

    #CognosAnalyticswithWatson


  • 2.  RE: How to Convert Julian Date

    Posted Tue January 21, 2020 12:57 PM

    Here is a tip I located using a Google search:

    https://erp-one.com/tech-tip-julian-date-conversion-oracle-sql/

    I have not tested.
    Good luck.



    ------------------------------
    Regards,
    Opher
    ------------------------------

    Original Message


  • 3.  RE: How to Convert Julian Date

    Posted Tue January 21, 2020 01:27 PM
    hi @Opher Banarie  thanks for the info.  I just tried it and we get an error of "ORA-01848: day of year must be between 1 and 365 (366 for leap year​​

    ------------------------------
    Jenifer Broughton
    ------------------------------

    Original Message


  • 4.  RE: How to Convert Julian Date

    Posted Tue January 21, 2020 04:25 PM
    Strange.
    Works for me:

    select a.check
    ,to_char(to_date(substr('115021',2,5),'YYDDD'),'YYYY-MM-DD') as "date"
    from db.table a
    fetch first 5 rows only

    returns 2015-01-21

    ------------------------------
    Regards,
    Opher
    ------------------------------

    Original Message


  • 5.  RE: How to Convert Julian Date

    Posted Tue January 21, 2020 11:38 PM

    It will depend on how the application chose to compute a their version of a Julian date.

    For example, in JDE their date was CYYDDD where C = Century YY = 2 digit year DD = the 3 digit number representing the day of the year (1 through 365 or 366 days on a leap year)

    Hence, knowing what the system does will influence what expression you would use.

    i.e. an ORACLE user might use this.

    to_date(to_char(1900 + floor(DATE / 1000)),'YYYY') + mod(DATE,1000) - 1

    if an ORACLE user was using the ORALCLE J-date (days since January 1, 4712 BC)  they might use to_date( number-to-string( Julian_Date), 'J') 

    and so on.



    ------------------------------
    NIGEL CAMPBELL
    ------------------------------

    Original Message


  • 6.  RE: How to Convert Julian Date

    Posted Wed January 22, 2020 07:42 AM
    Thanks @NIGEL CAMPBELL  for the additional information.  I will give this a try!​​

    ------------------------------
    Jenifer Broughton
    ------------------------------

    Original Message