ORA-01861: 날짜가 형식과 일치하지 않음 (Date Does Not Match Format)

ORA-01861: 날짜가 형식과 일치하지 않음 (Date Does Not Match Format)

ORA-01861 오류는 오라클 데이터베이스에서 발생하는 오류 중 하나로, "리터럴이 형식 문자열과 일치하지 않습니다(literal does not match format string)"라는 의미입니다. 이 오류는 주로 날짜 데이터를 다룰 때 발생하며, 문자열을 날짜 형식으로 변환하려고 시도했지만, 문자열의 형식이 지정된 날짜 형식과 일치하지 않을 때 발생합니다.

 

ORA-01861 오류의 주요 원인

• TO_DATE 함수 사용 시 형식 불일치 : TO_DATE 함수는 문자열을 날짜 형식으로 변환하는 함수입니다. 이 함수를 사용할 때, 변환하려는 문자열의 형식이 TO_DATE 함수에 지정한 형식 문자열과 정확히 일치해야 합니다. 그렇지 않으면 ORA-01861 오류가 발생합니다. 예를 들어, TO_DATE('2024-07-26', 'YYYY/MM/DD')는 오류를 발생시킵니다. 문자열은 'YYYY-MM-DD' 형식인데, 형식 문자열은 'YYYY/MM/DD' 형식이므로 불일치가 발생합니다.
• 암시적 형변환 시 형식 불일치 : 오라클은 특정 상황에서 암시적으로 문자열을 날짜 형식으로 변환하려고 시도합니다. 이때, 문자열의 형식이 데이터베이스의 기본 날짜 형식(NLS_DATE_FORMAT)과 일치하지 않으면 오류가 발생합니다.
• NLS 설정 차이 : 개발 환경과 운영 환경의 NLS 설정(특히 NLS_DATE_FORMAT)이 다를 경우, 개발 환경에서는 정상적으로 작동하던 쿼리가 운영 환경에서 ORA-01861 오류를 발생시킬 수 있습니다.

ORA-01861 오류 해결 방법

 

1. TO_DATE 함수 형식 문자열 확인 : TO_DATE 함수를 사용하는 경우, 변환하려는 문자열의 형식과 일치하는 정확한 형식 문자열을 사용해야 합니다. 자주 사용되는 형식 문자열은 다음과 같습니다.

• YYYY: 4자리 연도
• MM: 2자리 월
• DD: 2자리 일
• HH24: 24시간 형식의 시간
• MI: 분
• SS: 초

예를 들어, '2024-07-26 15:30:00'을 날짜로 변환하려면 TO_DATE('2024-07-26 15:30:00', 'YYYY-MM-DD HH24:MI:SS')와 같이 사용해야 합니다.

 

2. 명시적 형변환 사용 : 암시적 형변환에 의존하지 않고, 항상 TO_DATE 함수를 사용하여 명시적으로 문자열을 날짜로 변환하는 것이 좋습니다. 이렇게 하면 형식 불일치로 인한 오류를 예방할 수 있습니다.

 

3. NLS 설정 확인 및 변경 : NLS_DATE_FORMAT 설정을 확인하고 필요한 경우 변경합니다. 현재 세션의 NLS 설정은 다음과 같은 쿼리로 확인할 수 있습니다. 세션의 NLS 설정을 변경하려면 다음과 같은 명령어를 사용합니다. 하지만, 애플리케이션 전체의 NLS 설정을 변경하는 것은 다른 부분에 영향을 줄 수 있으므로, 쿼리 내에서 TO_DATE 함수를 사용하여 명시적으로 형변환하는 것이 더 권장되는 방법입니다.

 

4. 아래쿼리

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

 

5. 아래쿼리

SELECT value
FROM NLS_SESSION_PARAMETERS
WHERE parameter = 'NLS_DATE_FORMAT';

 

6. 입력 데이터 확인 : 데이터베이스에 저장된 날짜 데이터의 형식이 일관적인지 확인합니다. 만약 날짜 데이터의 형식이 다양하게 저장되어 있다면, 데이터를 정제하여 형식을 통일해야 합니다.

예시

• ORDERS 테이블에 order_date 컬럼이 있다고 가정해 보겠습니다.

-- 오류 발생
SELECT * FROM ORDERS WHERE order_date = '2024-07-26'; -- 암시적 형변환 시 오류 발생 가능성 있음

-- 해결 방법 (명시적 형변환 사용)
SELECT * FROM ORDERS WHERE order_date = TO_DATE('2024-07-26', 'YYYY-MM-DD');

-- 또 다른 예시
SELECT TO_DATE('2024/07/26', 'YYYY-MM-DD') FROM DUAL; -- 오류 발생 (형식 불일치)
SELECT TO_DATE('2024/07/26', 'YYYY/MM/DD') FROM DUAL; -- 정상 작동

 

※ ORA-01861 오류는 날짜 형식 불일치로 인해 발생하는 오류이므로, TO_DATE 함수를 사용할 때 형식 문자열을 정확하게 지정하고, 명시적 형변환을 사용하는 것이 중요합니다. 또한, NLS 설정을 확인하여 환경 차이로 인한 오류를 방지해야 합니다. 오류 메시지를 주의 깊게 읽고, 위에서 설명한 해결 방법을 참고하여 문제의 원인을 파악하고 해결할 수 있습니다.