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

Question

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!

4043917 · Answer

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

your code worked perfectly.

thank you

Gianni Ceresa · Answer

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 · Answer

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??