오라클 to_date 함수 사용시 ORA-01830, ORA-01839, ORA-01840, ORA-01841, ORA-01843, ORA-01847, ORA-01858 에러 대처방법

Oracle to_date( ) 함수는 varchar2 등 문자열 타입에 들어있는 날짜 데이터를 date 타입으로 변환하고자 할 때 사용합니다.

 

그런데, to_date( ) 함수를 사용할때 ORA-01839, ORA-01840, ORA-01841, ORA-01843, ORA-01847, ORA-01830, ORA-01858 등의 에러를 만나는 경우가 꽤 많습니다.

이유는 아래 TEST1 테이블과 같이 데이터에 잘못된 형식의 날짜가 들어있기 때문입니다.

 

SQL> select to_date(data, 'YYYY-MM-DD') from TEST1;
ERROR:
ORA-01843: not a valid month

ORA-01840: input value not long enough for date format

ORA-01841: (full) year must be between -4713 and +9999, and not be 0 

ORA-01847: day of month must be between 1 and last day of month

ORA-01839: date not valid for month specified

ORA-01858: a non-numeric character was found where a numeric was expected

ORA-01830: date format picture ends before converting entire input string

 

데이터가 몇개 안될때는 전체를 조회해서 하나씩 살펴보면 금방 찾을 수 있습니다.

하지만, 데이터가 십만건, 백만건 정도 되는 경우는 이 잘못된 데이터를 찾는 것이 쉽지 않습니다.

 

이때, 사용할 수 있는 방법이 오라클에서 제공하는 정규표현식 함수 regexp_like( ) 함수 입니다.

 

SQL> select data
from TEST1
where not regexp_like(data, '[0-9]{4}\-[0-9]{2}\-[0-9]{2}');

DATA
--------------------
2020-13-
cc123
09-11-

[0-9]{4}\-[0-9]{2}\-[0-9]{2} 는 "숫자4자리-숫자2자리-숫자2자리" 의 의미를 가진  정규표현식(Regulara Expression) 패턴입니다.

regexp_like 함수 앞에 not 을 붙였기 때문에, 위 패턴과 일치하지 않는 모든 데이터를 찾아내는 것입니다.

 

이런식으로 잘못된 데이터를 찾아서 해당 데이터를 제대로 된 값으로 바꿔주고나서 다시 to_date( ) 를 해주면 되겠습니다.

 

 

또다른 방법으로, 18c New Feature 를 이용하는 방법입니다.

Oracle 18c 부터는 to_date( ) 함수가 기능이 개선되어, 함수안에 on conversion error 라는 키워드를 넣을 수 있습니다. 날짜로 컨버전하다가 에러가 나면 지정한 default 값을 출력하라고 지정하는 것입니다.

 

SQL>
select data, to_date(data default '2030-01-01' on conversion error, 'YYYY-MM-DD')
from   TEST1;

DATA                 TO_DATE(DATADEFAULT
-------------------- -------------------
2020-07-22           2020-07-22 00:00:00
2020-07-23           2020-07-23 00:00:00
2020-08-01           2020-08-01 00:00:00
2020-13-             2030-01-01 00:00:00
cc123                2030-01-01 00:00:00
09-11-               2030-01-01 00:00:00
2020-11-44           2030-01-01 00:00:00

 

또 다른 방법으로, validate_conversion( ) 함수를 사용해서 체크해 볼 수 있습니다.

아래처럼 사용하고, "= 0" 대신 "=1" 로 하면 제대로 변환이 가능한 데이터만 조회할 수 있습니다.

SQL>
select data
from TEST1
where validate_conversion(data as date, 'YYYY-MM-DD') = 0;

DATA
--------------------
2020-13-
cc123
09-11-
2020-11-44

 

on conversion error 와 validate_conversion 함수는 Oracle 18c 의 신기능이기 때문에 18c, 19c 등의 DB에서만 사용할 수 있는 방법입니다.

11g, 12c 등에서는 사용할 수 없습니다.