Oracle Analytics Cloud and Server

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

Date conversion issue

Received Response
181
Views
5
Comments
Rank 4 - Community Specialist

Hi Team,

We have column board_date column with wrong format data (ex: 20200329SC20100.000 SKP) data type for this varchar2, we have to show this data as date 29/03/2020.

Can someone suggest on this we don’t have access for database and report level changes has to be done in RPD.

version we are using :OBIEE 12c 12.2.1.4.0

Thanks,

Welcome!

It looks like you're new here. Sign in or register to get started.

Answers

  • You can do a SUBSTRING to get the date part out of that string, and on top of that you use TO_DATETIME to convert it to a date.

    TO_DATETIME("Time"."Calendar Date",'yyyy/mm/dd mm:ss')

    And in the same time raise a request to have a proper date in the database, because that will perform poorly as you can imagine (executing those transformation on every single row at every query).

  • Rank 4 - Community Specialist

    Hi Gianni,

    Thanks for the reply.

    I have tested above logic in report level for the date column its working, but I tried with changing diff format like DD/MM/YYYY, YYYY/MM/DD, MM/DD/YYYY.

    But It was showing data in only 1 format (ex:05/28/2014), I’m looking for the format DD/MM/YYYY(28/5/2014).

    can you suggest on this.

    Thanks,

  • It is working as expected, you are just seeing a problem where there isn't one.

    3913018 wrote:....changes has to be done in RPD.

    You asked to turn a string into a date in the RPD, once you do that the column is of type date or timestamp. The visualization of a column of type date or timestamp has to be configured in the analysis on the column properties.

    Or are you asking to get another string showing a date from a string representing a date already? If that's the case, you maybe want to do a step back and check the supported data types by OBIEE and why proper data types make a huge difference.

  • Rank 4 - Community Specialist

    I want to do changes in RPD level, using below logic in the date field in BMM layer is enough or I have to change the data type for physical column as well, bcoz date field is varchar data type.

    TO_DateTime(SUBSTRING("Sales"."Date" FROM 1 FOR 8),'dd/mm/yyyy')

  • In the physical layer the data type is the type you get, if it's a string it must be VARCHAR. If you don't define it as a VARCHAR you can't use string functions on it.

    Just keep in mind this is a workaround: you will pay the price for it every time you use that column.

    The solution is to have the database giving you the correct attribute with the right type.

Welcome!

It looks like you're new here. Sign in or register to get started.