Categories
Date conversion issue

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,
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).
0 -
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,
0 -
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.
0 -
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')
0 -
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.
0