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