I have a source table with below structure:
create table customer_info
(customer_num number,
birth_date char(10))
Unfortunately the birth_date
column data type is char(10)
instead of date
. Some example data for this table would be like below:
customer_num | birth_date
--------------------------------
1 | 2001/01/01
1 | 2010/01/01
1 | 2021/01/01
1 | 12hhhhuu6 --> Incorrect date
1 | 2001/01/01
1 | 2001/23/01 --> Incorrect date
what I've done is writing a function to evaluate every single record and return it's correct format but as you know , using a function for every single record is not a good idea and it somehow kills performance. I was wondering if you could suggest a better way for this.
create or replace function new_to_date_en(d varchar2) return DATE
is
v_date date;
begin
select to_date(d,'yyyy/mm/dd' ) into v_date from dual;
return to_date(d,'yyyy/mm/dd');
exception when others then return to_dateto_date('2021/03/07', 'yyyy/mm/dd');
end;
Using the function:
select customer_num,
new_to_date_en(birth_date)
from customer_info;
There is a way in T-SQL COALESCE(TRY_CONVERT(date, @date, 111), '2012-01-01')
. Is there a similar way in oracle plsql?
Thanks in advance
Best Answer
TRY_CONVERT
is a function that is called for every record, how is that different?Below works since 12.2:
On your version, you can use your function.