Date conversion issue

User_JZKI6

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,

Gianni Ceresa

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).

User_JZKI6

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,

Gianni Ceresa

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.

User_JZKI6

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')

Gianni Ceresa

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.