ORA-01861: 리터럴이 형식 문자열과 일치하지 않음 ( literal does not match format string )

NLS_DATE_FORMAT, NLS_TIME_FORMAT, NLS_TIMESTAMP_FORMAT에 설정된 포맷과 일치하지 않은 문자열을 DATE형으로 변환하는 경우 암묵적 변환에 있어서 오류가 발생합니다. 이 글에서는 ORA-01861 오류가 발행하는 예시와 이를 해결하기 위한 포맷 변경 그리고 실행 결과 확인에 대해 소개합니다.

‘nls_language = KOREAN’ 기본 설정에서 확인

다음은 Oracle Database의 ‘nls_language = KOREAN’ 인스턴스에 접속해서 NLS_DATE_FORMAT, NLS_TIME_FORMAT, NLS_TIMESTAMP_FORMAT의 관련 설정을 확인 했을 때 값을 확인하는 명령입니다.

$ export NLS_LANG=KOREAN_KOREA.KO16MSWIN949

$ sqlplus / as sysdba
SQL> show parameters nls
NAME                  VALUE
--------------------- ------------------------------
... 중간 생략 ...
nls_date_format       RR/MM/DD
... 중간 생략 ...
nls_time_format       HH24:MI:SSXFF
nls_timestamp_format  RR/MM/DD HH24:MI:SSXFF

위 설정에서 ‘2021/12/12 12:12:12’의 문자열 포맷을 DATE형으로 변환하려고 한다면 다음과 같이 오류가 발생합니다.

SQL> select to_date('2021/12/12 12:12:12') from dual;
select to_date('2021/12/12 12:12:12') from dual
               *
1행에 오류:
ORA-01861: 리터럴이 형식 문자열과 일치하지 않음

‘nls_language = AMERICAN’ 기본 설정에서 확인

다음은 Oracle Database의 ‘nls_language = AMERICAN’ 인스턴스에 접속해서 NLS_DATE_FORMAT, NLS_TIME_FORMAT, NLS_TIMESTAMP_FORMAT의 관련 설정을 확인 했을 때 값을 확인하는 명령입니다.

$ export NLS_LANG=AMERICAN_AMERICA.US7ASCII

$ sqlplus scott/tiger
SQL> COLUMN parameter FORMAT A20;
     COLUMN value FORMAT A30;
     select parameter , value from v$nls_parameters 
     where parameter in ( 'NLS_DATE_FORMAT','NLS_TIME_FORMAT','NLS_TIMESTAMP_FORMAT');
PARAMETER            VALUE
-------------------- ------------------------------
NLS_DATE_FORMAT      DD-MON-RR
NLS_TIME_FORMAT      HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM

위 설정에서 ‘2021/12/12 12:12:12’의 문자열 포맷을 DATE형으로 변환하려고 한다면 다음과 같이 오류가 발생합니다.

SQL> select to_date('2021/12/12 12:12:12') from dual;
select to_date('2021/12/12 12:12:12') from dual
               *
ERROR at line 1:
ORA-01861: literal does not match format string

포맷 변경 후 실행

NLS_DATE_FORMAT, NLS_TIME_FORMAT, NLS_TIMESTAMP_FORMAT의 포맷을 다음과 같이 ‘2021/12/12 12:12:12’의 문자열 포맷에 맞도록 변경합니다.

SQL> ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS';
세션이 변경되었습니다.
SQL> ALTER SESSION SET NLS_TIME_FORMAT='HH24:MI:SSXFF';
세션이 변경되었습니다.
SQL> ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY/MM/DD HH24:MI:SSXFF';
세션이 변경되었습니다.

변경된 포맷으로 ‘2021/12/12 12:12:12’의 문자열 포맷을 DATE형으로 변환하면 포맷이 일치하기 때문에 다음과 같이 정상 출력 되었음을 확인할 수 있습니다.

SQL>  select to_date('2021/12/12 12:12:12') as dateformat from dual;
DATEFORMAT
-------------------
2021/12/12 12:12:12