ORA-01841 해결: 날짜 값 범위 오류 점검과 변환 표준화

ORA-01841 해결: 날짜 값 범위 오류 점검과 변환 표준화

ORA-01841은 날짜/시간 변환 과정에서 연도(YYYY) 값이 0이거나 허용 범위를 벗어난 값이 들어올 때 발생하는 대표적인 오류입니다. 특히 문자열을 DATE나 TIMESTAMP로 변환하는 SQL/PLSQL, 또는 애플리케이션/ETL에서 넘어오는 파라미터 처리에서 자주 나타납니다.

실무 기준으로 보면
ORA-01841의 원인은 “오라클이 날짜를 이해 못했다”가 아니라,
“입력값(문자열/바인드)이 기대한 포맷·범위가 아니다”인 경우가 대부분입니다.
그래서 쿼리를 고치기 전에 문제 입력값 1건을 먼저 잡는 게 가장 빠릅니다.

개요

Oracle에서 DATE와 TIMESTAMP는 내부적으로 유효 범위를 갖고 있고, 문자열을 날짜로 변환할 때는 TO_DATE, TO_TIMESTAMP 및 세션의 NLS 포맷(NLS_DATE_FORMAT 등)에 영향을 받습니다. 이 과정에서 연도 0, 너무 큰/작은 연도, 잘못된 자리수, 포맷 불일치가 발생하면 ORA-01841이 터질 수 있습니다.

환경

• DB: Oracle Database (11g~19c/21c 전반)
• 유형: SQL에서 문자열→날짜 변환, 바인드 변수 날짜 처리, ETL/배치 적재, CSV/엑셀 입력
• 영향 요소: TO_DATE/TO_TIMESTAMP 포맷 모델, 세션 NLS 설정, 애플리케이션 바인드 타입

증상

대표적인 발생 지점은 아래와 같습니다.

• TO_DATE(col, 'YYYYMMDD'), TO_TIMESTAMP(:p, ...) 수행 시 ORA-01841
• 바인드 변수로 날짜가 들어오는데, 실제로는 문자열/숫자로 넘어와 암묵 변환이 발생
• ETL 적재 중 일부 레코드(특정 행)에서만 실패
• 환경(개발/운영)마다 재현이 달라짐 → NLS 설정 차이 가능성

-- 흔한 패턴(암묵 변환이 끼어드는 경우)
WHERE date_col >= '20260101'    -- 문자열 비교/암묵 변환 위험
-- 또는
INSERT INTO t(dt) VALUES('00000101'); -- 연도 0 또는 잘못된 값

1차 점검

아래 4가지를 먼저 확정하면 원인 파악이 급격히 빨라집니다.

점검 항목	확인 포인트
문제 입력값(원본)	실패한 한 건의 날짜 문자열/값(예: 0000-01-01, 99999999, 공백 포함 등)
변환 코드	TO_DATE/TO_TIMESTAMP 포맷이 입력과 정확히 일치하는지
NLS 설정	NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT, NLS_CALENDAR 차이 여부
바인드 타입	애플리케이션이 날짜를 DATE/TIMESTAMP로 바인딩하는지, 문자열로 보내는지

NLS 설정 확인

SELECT * FROM nls_session_parameters
WHERE parameter IN ('NLS_DATE_FORMAT','NLS_TIMESTAMP_FORMAT','NLS_TIMESTAMP_TZ_FORMAT','NLS_CALENDAR');

SELECT * FROM nls_database_parameters
WHERE parameter IN ('NLS_DATE_FORMAT','NLS_TIMESTAMP_FORMAT','NLS_TIMESTAMP_TZ_FORMAT','NLS_CALENDAR');

관리자 입장에서
개발에서는 되는데 운영에서만 터지는 ORA-01841은 NLS 차이가 원인인 경우가 많습니다.
특히 “암묵 변환”이 있으면, 같은 SQL이 세션 포맷에 따라 성공/실패가 갈립니다.

심화 분석

ORA-01841의 대표 원인을 케이스별로 정리하면 아래와 같습니다.

케이스 A: 연도 0(YYYY=0000) 또는 자리수 깨짐

입력값에 0000년이 포함되거나, 연도 자리수가 모자라/넘쳐 변환이 실패합니다.
예: 0000-12-31, 00000101, 0/1/1

케이스 B: 포맷 불일치

입력은 YYYY-MM-DD인데 포맷을 YYYYMMDD로 주는 등 불일치가 있을 때 발생합니다.
공백/로케일(월 이름)/타임존까지 포함되면 실패 가능성이 커집니다.

케이스 C: 암묵 변환(Implicit Conversion)

문자열을 DATE 컬럼과 비교하거나, 문자열을 DATE 컬럼에 넣을 때 세션 NLS 포맷으로 변환됩니다.
세션 포맷이 바뀌면 동일 SQL이 터질 수 있습니다.

케이스 D: ETL/파일 입력의 더러운 값

NULL 대신 00000000, 99999999, 공백, 잘못된 구분자 등이 섞여 들어오는 경우가 많습니다.
“99/99/9999” 같은 더미 날짜가 특히 위험합니다.

실무 포인트
ORA-01841을 해결하는 가장 빠른 길은 “실패한 입력값 1건”을 재현 환경에서 그대로 변환해보는 것입니다.
그 한 건을 잡으면, 어떤 규칙으로 필터/정정해야 하는지가 바로 나옵니다.

복구

복구는 크게 3갈래입니다: (1) 암묵 변환 제거, (2) 입력값 정제/검증, (3) 바인딩 표준화. 운영 환경에서 안전하게 적용하려면 “임시 우회”보다 “표준화”를 권장합니다.

1) 암묵 변환 제거(명시적 변환 사용)

-- 나쁜 예(세션 NLS에 따라 동작이 달라짐)
WHERE dt >= '2026-06-01'

-- 좋은 예(입력과 포맷을 명시)
WHERE dt >= TO_DATE('2026-06-01', 'YYYY-MM-DD')

2) 더러운 입력값 필터링(ETL/적재 전)

-- 예시: 8자리 날짜(YYYYMMDD)만 허용하고, 00000000/공백은 제외
WHERE REGEXP_LIKE(src_dt, '^[0-9]{8}$')
  AND src_dt NOT IN ('00000000')
  AND SUBSTR(src_dt,1,4) <> '0000'

3) 안전 변환(실패 시 NULL 처리) 패턴

-- 12.2+ 환경에서 유용: 변환 실패 시 NULL
SELECT TO_DATE(src_dt DEFAULT NULL ON CONVERSION ERROR, 'YYYYMMDD')
FROM   src;

주의
“변환 실패 시 NULL”은 장애는 막지만 데이터 품질 이슈를 숨길 수 있습니다.
운영에서는 NULL 처리와 함께 “오류 레코드 별도 적재/리포트”를 같이 설계하는 편이 안전합니다.

4) 애플리케이션 바인딩 표준화

-- 권장 원칙(개념)
- 애플리케이션은 날짜 파라미터를 문자열로 만들지 말고 DATE/TIMESTAMP 타입으로 바인딩
- SQL 내부에서 문자열 날짜를 만들 필요가 있으면 포맷을 강제(TO_DATE/TO_TIMESTAMP)
- 세션 NLS에 기대는 로직(암묵 변환)을 제거

재발 방지

• 입력 표준: 날짜는 한 가지 포맷(예: YYYY-MM-DD 또는 YYYYMMDD)로만 저장/전달
• 암묵 변환 금지: DATE/TIMESTAMP 컬럼에 문자열 비교/대입을 하지 않도록 코딩 규칙화
• 데이터 품질 규칙: 0000년, 00000000, 99999999 등 더미 값을 명시적으로 금지
• 에러 레코드 격리: 적재 실패 데이터는 별도 테이블/파일로 분리해 원인 추적 가능하게 유지
• NLS 고정: 배치/ETL 계정은 세션 시작 시 NLS 포맷을 명시적으로 세팅(필요 시)

실제 사용 시
날짜 오류는 결국 “데이터 규칙 부재”에서 반복됩니다.
한 번 터진 ORA-01841은, 입력 데이터 규칙과 변환 표준을 문서로 고정하지 않으면 다시 터질 확률이 높습니다.