Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

OBIEE - day of year to date - cannot figure out!

Received Response
201
Views
4
Comments
4043917
4043917 Rank 1 - Community Starter
edited Aug 13, 2024 5:25PM in Oracle Analytics Cloud and Server

hello all - hoping someone here has tried this and can offer some help.

i am trying to do the following in OBIEE:

select cast(DATEADD(Day,cast(substring('19087',3,3) as int),DATEADD(year,concat('20',left('19087',2))-1900,0)) as date) as mdt

what this does in SQL is takes the year, left 2 digits, and the DAY of the year, 087, and turns it in to 2019-03-29

i CANNOT figure this out...at all in OBIEE.

can anyone help?

thank you!

Answers

  • Gianni Ceresa
    Gianni Ceresa Mod

    Something like this: TIMESTAMPADD(SQL_TSI_DAY, CAST(RIGHT('19087', 3) as INT), TO_DATETIME('20' || LEFT('19087', 2) || '-01-01', 'yyyy-mm-dd'))

    But ideally you must have a proper time dimension giving you various formats representing the dates based on your needs.

  • 4043917
    4043917 Rank 1 - Community Starter

    thank you so much for your reply Gianni!!

    when trying to use this in my Column Formula window i am receiving an error:

    Formula syntax is invalid.

    [nQSError: 10058] A general error has occurred. Please have your System Administrator look at the log for more details on this error. (HY000)

    [nQSError: 43113] Message returned from OBIS. Please have your System Administrator look at the log for more details on this error. (HY000)

    [nQSError: 27042] Function TO_DATE is not defined by administrator. Please have your System Administrator look at the log for more details on this error. (HY000)

    SQL Issued: SELECT TO_DATE('20' || LEFT('19087',2) || '-01-01','yyyy-mm-dd') FROM "tblS"

    OK (Ignore Error)

    i'm guessing this is something i will have to ask the admin about??

  • Gianni Ceresa
    Gianni Ceresa Mod

    So it's time to go back to

    Post all the details about the context, the environment, the tool (exact version including bundle patches) etc. And also what's your database behind that model.

  • 4043917
    4043917 Rank 1 - Community Starter

    Sorry bout that - turns out there was some bad data i had to "CASE WHEN" around.

    your code worked perfectly.

    thank you