Data Conversion and Validation(2/3)

게시일: Jun 03, 2020 1:11:8 AM

select cast(col_1 as date default '2999-12-31' on conversion error, 'yyyy-mm-dd') col_1 -- 에러가 발생하면 '2999-12-31' 출력

from (select '6789' col_1 from dual union all

select 'test' col_1 from dual union all

select '2020-05-12' col_1 from dual union all

select '2020-12-32' col_1 from dual

)

;

--------------------

COL_1

--------------------

2999-12-31 00:00:00

2999-12-31 00:00:00

2020-05-12 00:00:00

2999-12-31 00:00:00

--------------------

;

select cast(col_1 as timestamp default '2999-12-31' on conversion error, 'yyyy-mm-dd') col_1 -- 에러가 발생하면 '2999-12-31' 출력

from (select '6789' col_1 from dual union all

select 'test' col_1 from dual union all

select '2020-05-12' col_1 from dual union all

select '2020-12-32' col_1 from dual

)

;

------------------------------

COL_1

------------------------------

2999-12-31 00:00:00.000000000

2999-12-31 00:00:00.000000000

2020-05-12 00:00:00.000000000

2999-12-31 00:00:00.000000000

------------------------------

;