Oracle YYYYMMDD 날짜 비교, TO_DATE 없이 해결하는 법

문제: "지정된 월에 대한 날짜가 부적합합니다"

문자열로 저장된 날짜 컬럼을 TO_DATE로 변환해서 비교하는 쿼리를 작성했다.

WHERE TO_DATE(EXP_END_DT, 'YYYYMMDD') >= TO_DATE('20260430', 'YYYYMMDD')

정상적인 날짜 데이터만 있을 때는 문제없이 돌아간다. 그런데 테이블에 99990413이나 20990413 같은 값이 들어 있으면 ORA-01839 에러가 발생한다. 9999년 4월 13일은 유효한 날짜지만, 포맷에 따라 다르게 파싱될 수 있고, 실무에서는 이보다 더 엉뚱한 sentinel value가 들어 있는 경우도 많다.

원인: Oracle은 WHERE 절 평가 순서를 보장하지 않는다

직관적으로는 "조건에 맞는 행만 골라낸 뒤 함수를 적용할 것"이라고 생각하기 쉽다. 하지만 Oracle optimizer는 실행 계획을 자체적으로 결정하며, WHERE 절 내 조건의 평가 순서를 보장하지 않는다.

즉, EXP_END_DT = '99990413'인 행에 TO_DATE가 먼저 적용될 수 있고, 그 순간 유효하지 않은 날짜 파싱이 시도되면서 에러가 터진다.

해결: TO_DATE를 쓰지 않으면 된다

WHERE EXP_END_DT >= '20260430'

이게 전부다. YYYYMMDD 포맷의 문자열은 문자열 비교만으로 정확한 날짜 대소 비교가 가능하다.

왜 문자열 비교가 날짜 비교와 동일한가

문자열 비교(lexicographic comparison)는 왼쪽부터 한 글자씩 ASCII 값을 비교한다. 이 방식이 시간순과 정확히 일치하려면 두 가지 조건이 필요하다.

첫째, 큰 시간 단위가 앞에 와야 한다. YYYYMMDD는 연 → 월 → 일 순서로 배치되어 있다. 문자열 비교는 왼쪽부터 시작하므로, 가장 먼저 비교되는 것이 가장 큰 단위인 연도다. 연도가 같으면 월, 월이 같으면 일을 비교하게 되어 자연스럽게 시간순이 된다.

둘째, 자릿수가 고정되어야 한다. 1월은 01, 9월은 09로 zero-padding 되어 있다. 만약 YYYYMD 같은 가변 자릿수 포맷이라면 202511(1월 1일)과 202529(2월 9일)를 비교할 때 5 > 2로 잘못된 결과가 나온다. YYYYMMDD는 항상 8자리 고정이므로 이 문제가 없다.

숫자 문자 '0'부터 '9'까지의 ASCII 코드는 0x30~0x39로 수치 순서와 동일하다. 이 세 가지가 합쳐져서, YYYYMMDD 포맷의 문자열 사전순 = 시간순이 성립한다.

이 원리가 깨지는 포맷

모든 날짜 포맷에 적용되는 건 아니다.

DDMMYYYY는 일이 맨 앞에 오므로 31012024(2024-01-31)가 01122025(2025-12-01)보다 크다고 판정된다. MM/DD/YYYY도 마찬가지로 월이 최상위 비교 대상이 되어 연도 간 비교가 깨진다. 반면 YYYY-MM-DD처럼 구분자가 들어가더라도 구분자 위치가 고정이면 문자열 비교가 유효하다.

부수적 이점: 인덱스 활용

TO_DATE(EXP_END_DT, 'YYYYMMDD')는 컬럼에 함수를 적용하는 것이므로, 해당 컬럼에 일반 index가 걸려 있어도 index range scan을 사용할 수 없다. function-based index를 별도로 생성하지 않는 한, full table scan으로 빠진다.

문자열 비교 EXP_END_DT >= '20260430'은 컬럼을 그대로 비교하므로 기존 index를 활용할 수 있다.

정리

YYYYMMDD 고정 8자리 포맷이라면 TO_DATE 변환은 불필요하다. 제거하면 유효하지 않은 데이터에 의한 에러를 피하고, 인덱스 활용도 가능해진다. 날짜 문자열 비교가 동작하는 원리는 "큰 단위가 앞 + 고정 자릿수 + 숫자 ASCII 순서 = 수치 순서"라는 세 가지 조건이 맞아떨어지기 때문이다.