[oracle] ORA-01861: literal does not match format string

개발서버에서 개발을 하다가 개발한 소스를 운영환경에 올릴 경우,

TO_CHAR, TO_DATE 메서드를 사용하는 부분에서 가끔씩 아래와 같은 에러가 발생한다.

(ex) TO_CHAR(TO_DATE(‘20170101’),'yyyy-mm-dd')

개발서버에서는 분명 잘 돌아가던 것인데 왜 에러가 날까? 

ORA-01861 : literal does not match format string

에러메세지를 구글 번역기에 돌려보면 "리터럴이 형식 문자열과 일치하지 않습니다." 로 번역된다.

[원인]

TO_DATE를 사용할때, 두번째 인수를 셋팅하지 않으면 오라클 데이터베이스에 설정되어있는 날짜 설정에 따라 변환되는데, 

해당 에러는 개발 DB의 문자셋과 언어설정, 날짜설정이 운영DB의 설정 값과 다르기 때문에 나타나는 것이다.

DB의 문자셋과 언어설정, 날짜설정 값은 아래 쿼리를 실행시켜 보면 알 수 있다.

SELECT *

FROM NLS_SESSION_PARAMETERS

WHERE PARAMETER IN ('NLS_LANGUAGE', 'NLS_DATE_FORMAT', 'NLS_DATE_LANGUAGE')

[개발서버]

NLS_LANGUAGE = AMERICAN

NLS_DATE_FORMAT = YYYYMMDD

NLS_DATE_LANGUAGE = AMERICAN

[운영서버]

NLS_LANGUAGE = AMERICAN

NLS_DATE_FORMAT = DD-MON-RR

NLS_DATE_LANGUAGE = AMERICAN

아마 개발서버와 운영서버의 설정값이 상이할 것이다.

TO_DATE(‘20170101’)이 개발서버에서는 먹힐지 몰라도 운영 환경에서는 ORA-01861 에러가 떨어질 것이다~

반대로 TO_DATE('2017-11-22')는 운영환경에서는 먹힐지 몰라도 개발환경에서는 먹히지 않을것이다~

해당 문제를 해결하기 위한 방법으로 2가지를 제시한다.

[해결방법1]

개발서버의 문자셋, 언어설정, 날짜설정을 운영DB의 설정값과 동일하게 SETTING 

[해결방법2]

TO_CHAR과 TO_DATE의 두번째 인수를 넣어주기

TO_DATE('20181011','YYYYMMDD')   => '20181011'이라는 문자열은 'YYYYMMDD' 형식임을 지정하고 문자열을 날짜 형식으로 변환

TO_DATE('2018-10-11','YYYY-MM-DD') => '2018-10-11'이라는 문자열은 'YYYY-MM-DD' 형식임을 지정하고 문자열을 날짜 형식으로 변환

TO_CHAR(SYSDATE,'YYYY-MM-DD')  => 날짜형식을 문자열로 변환할때, 어떤 모양새로 변환할것인지 지정.

 

[테스트]

운영환경에서 테스트 결과 아래와 같은 결과가 출력된다.

1. SELECT TO_DATE(‘20170101’) FROM DUAL;

=> ORA-01861: literal does not match format string.

2. SELECT TO_DATE(sysdate) from dual;

=> 2019-01-07   (long data format)

3. SELECT TO_DATE(‘20170101’,’yyyymmdd’) FROM DUAL;

=> 2017-01-01   (long data format)

4. SELECT TO_CHAR(to_date(‘20170101’,’yyyymmdd’)) FROM DUAL;

=> ‘01-JAN-17’(varchar)

5. SELECT TO_CHAR(to_date(‘2017-01-01’,’yyyy-mm-dd’)) FROM DUAL;

=> ‘01-JAN-17’(varchar)

6.  SELECT TO_CHAR(to_date(‘20170101’,’yyyymmdd’),’yyyy-mm-dd’) FROM DUAL;

=> ‘2017-01-01’

7.  SELECT TO_CHAR(to_date(‘2017-01-01’,’yyyy-mm-dd’),’yyyy/mm/dd’) FROM DUAL;

=> ‘2017/01/01’

p.s.  쿼리를 짤때 TO_CHAR과 TO_DATE에 문자열 형식을 꼭꼭 지정하는 습관을 들인다면, 해당 에러는 이제 안녕할수 있지 않을까싶다아