Oracle 날짜 비교 방법과 자주 하는 실수 정리

Oracle 날짜 비교에서 흔히 막히는 상황

분명 오늘 등록된 데이터가 있는데 날짜 조건으로 조회하면 빠진다. 또는 문자열로 날짜를 비교했더니 결과가 이상하다. Oracle 날짜 비교에서 가장 자주 나오는 증상이다. 원인은 대부분 한 가지로 모인다. Oracle의 DATE 타입은 날짜뿐 아니라 시·분·초까지 함께 저장한다는 점이다.

예를 들어 2024-03-15 14:30:25처럼 시간이 같이 들어 있는데, 화면이나 쿼리에서는 2024-03-15까지만 보고 비교하다 보니 어긋난다. 여기서부터 풀어 본다.

원인 요약

증상	원인	확인 방법
오늘 데이터가 조회 안 됨	DATE에 시분초가 있어 = 비교가 안 맞음	TO_CHAR로 시분초까지 출력해 확인
BETWEEN에서 마지막 날 누락	끝 날짜가 00:00:00로 해석됨	끝 조건 시간 부분 확인
문자열 비교가 이상함	DATE를 문자열로 암묵 변환	비교 대상 타입 확인
TO_DATE 에러	포맷 문자열과 실제 값 불일치	입력 값과 포맷 패턴 대조
인덱스를 안 탐	컬럼에 함수(TRUNC 등) 적용	실행계획에서 인덱스 사용 여부 확인

DATE 타입의 시분초부터 이해하기

1. 같은 날인데 = 비교가 안 맞는 이유

가장 흔한 함정이다. reg_date = TO_DATE('2024-03-15','YYYY-MM-DD')로 비교하면, 우변은 2024-03-15 00:00:00이다. 그런데 실제 데이터는 2024-03-15 14:30:25처럼 시간이 붙어 있어 같지 않다. 그래서 분명히 그날 데이터인데 빠진다.

-- 데이터에 시분초가 있는지 먼저 확인
SELECT TO_CHAR(reg_date, 'YYYY-MM-DD HH24:MI:SS') AS reg_date
FROM   board
WHERE  ROWNUM <= 5;

시분초가 0이 아니라면 단순 = 비교는 거의 맞지 않는다. 하루 전체를 조회하려면 범위로 접근해야 한다.

2. 하루 전체를 조회하는 올바른 범위 조건

특정 날짜의 데이터 전체를 가져오려면 그날 00:00:00부터 다음 날 00:00:00 직전까지를 범위로 잡는다. 가장 안전한 형태는 다음과 같다.

-- 2024-03-15 하루치 전체 조회
SELECT *
FROM   board
WHERE  reg_date >= TO_DATE('2024-03-15','YYYY-MM-DD')
AND    reg_date <  TO_DATE('2024-03-16','YYYY-MM-DD');

시작은 >=, 끝은 다음 날 <로 잡는 게 핵심이다. 이렇게 하면 그날의 23:59:59까지 모두 포함되면서 다음 날 00:00:00은 제외된다. 시분초를 신경 쓰지 않아도 돼서 실무에서 가장 많이 쓰는 패턴이다.

BETWEEN을 쓸 때 하루가 누락되는 문제

BETWEEN은 양쪽 끝을 포함하지만 날짜에서는 함정이 있다. 아래처럼 쓰면 마지막 날 데이터가 거의 빠진다.

-- 흔한 실수: 끝 날짜가 00:00:00로 해석됨
SELECT *
FROM   board
WHERE  reg_date BETWEEN TO_DATE('2024-03-01','YYYY-MM-DD')
                    AND TO_DATE('2024-03-31','YYYY-MM-DD');
-- → 3월 31일 00:00:01 이후 데이터는 빠짐

끝 조건 2024-03-31이 2024-03-31 00:00:00으로 해석되기 때문에, 31일 낮에 등록된 데이터는 BETWEEN 범위를 벗어난다. 해결은 끝 날짜를 다음 날로 두고 <를 쓰거나, 끝 시간을 명시하는 것이다.

-- 방법 A: 다음 날 미만 (권장)
WHERE reg_date >= TO_DATE('2024-03-01','YYYY-MM-DD')
AND   reg_date <  TO_DATE('2024-04-01','YYYY-MM-DD');

-- 방법 B: 끝 시간을 명시 (초 단위 누락 주의)
WHERE reg_date BETWEEN TO_DATE('2024-03-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
                   AND TO_DATE('2024-03-31 23:59:59','YYYY-MM-DD HH24:MI:SS');

방법 B는 직관적이지만 23:59:59.5 같은 밀리초가 있는 환경(TIMESTAMP 등)에서는 미세하게 누락될 수 있다. 그래서 범위 조회는 방법 A(다음 날 미만)를 기본으로 삼는 편이 안전하다.

문자열 날짜와 DATE 비교

실무에서는 컬럼이 DATE인 경우와 VARCHAR2로 '20240315'처럼 문자열로 저장된 경우가 섞여 있다. 둘을 비교할 때 타입을 맞추지 않으면 암묵적 형변환이 일어나 결과가 어긋나거나 인덱스를 못 탄다.

-- 컬럼이 DATE인 경우: 비교 대상도 DATE로 변환
SELECT * FROM board
WHERE  reg_date >= TO_DATE('20240315','YYYYMMDD');

-- 컬럼이 VARCHAR2('YYYYMMDD')인 경우: 문자열끼리 비교
SELECT * FROM board
WHERE  reg_ymd >= '20240315';

핵심은 컬럼 타입에 비교 대상을 맞추는 것이다. DATE 컬럼에 문자열을 그냥 넣으면 Oracle이 NLS 설정에 따라 변환을 시도하는데, 환경에 따라 동작이 달라질 수 있어 ORA-01861(리터럴이 형식 문자열과 불일치) 같은 에러가 나기도 한다. 비교 대상을 명시적으로 변환하는 습관이 안전하다.

TRUNC 사용과 인덱스 주의

날짜의 시분초를 떼고 비교할 때 TRUNC를 쓰는 경우가 많다. TRUNC(reg_date)는 시간을 00:00:00으로 잘라 준다.

-- 오늘 데이터 조회 (TRUNC로 시분초 제거)
SELECT *
FROM   board
WHERE  TRUNC(reg_date) = TRUNC(SYSDATE);

직관적이지만 주의할 점이 있다. reg_date 컬럼에 함수를 씌우면, 그 컬럼에 일반 인덱스가 있어도 인덱스를 타지 못한다. 데이터가 많은 테이블에서는 성능이 크게 떨어질 수 있다. 그래서 대용량 조회에서는 컬럼에 함수를 씌우지 않고 범위 조건으로 바꾸는 편이 낫다.

-- 인덱스를 살리는 형태 (컬럼에 함수 미적용)
SELECT *
FROM   board
WHERE  reg_date >= TRUNC(SYSDATE)
AND    reg_date <  TRUNC(SYSDATE) + 1;

여기서 TRUNC(SYSDATE) + 1은 내일 00:00:00이다. Oracle에서 DATE에 숫자를 더하면 일(day) 단위로 계산되므로, +1은 하루 뒤를 의미한다. 함수는 비교 대상(SYSDATE) 쪽에만 쓰고 컬럼은 그대로 두는 게 요령이다. 다만 함수 기반 인덱스(function-based index)가 따로 걸려 있다면 TRUNC 방식도 인덱스를 탈 수 있으므로, 실제로는 실행계획을 확인해 판단한다.

실무에서 자주 하는 실수

• DATE 컬럼을 = TO_DATE('날짜')로 비교해 시분초 때문에 그날 데이터를 통째로 놓치는 경우.
• BETWEEN의 끝 날짜를 그대로 써서 마지막 날 데이터가 빠지는 경우.
• DATE 컬럼과 문자열을 직접 비교해 NLS 설정에 따라 결과가 달라지거나 에러가 나는 경우.
• 대용량 테이블에서 TRUNC(컬럼)을 써서 인덱스를 못 타 조회가 느려지는 경우.
• TO_DATE의 포맷 문자열과 실제 입력 값이 달라(예: 값은 2024/03/15인데 포맷은 YYYY-MM-DD) 변환 에러가 나는 경우.

FAQ

Q1. 오늘 등록된 데이터인데 날짜로 조회하면 안 나옵니다.

DATE 컬럼에 시분초가 들어 있는데 = TO_DATE('오늘')로 비교했을 가능성이 크다. reg_date >= TRUNC(SYSDATE) AND reg_date < TRUNC(SYSDATE)+1 형태로 범위 조회한다.

Q2. BETWEEN으로 한 달을 조회했는데 마지막 날이 빠집니다.

끝 날짜가 00:00:00으로 해석돼 그날 낮 데이터가 제외된 것이다. 끝 조건을 다음 날 미만(< 다음달1일)으로 바꾼다.

Q3. 문자열로 저장된 날짜는 어떻게 비교하나요?

YYYYMMDD처럼 정렬 가능한 형식이면 문자열끼리 비교해도 된다. 단, 컬럼이 DATE인지 VARCHAR2인지 먼저 확인하고 타입을 맞춘다.

Q4. TRUNC를 쓰면 왜 느려지나요?

컬럼에 함수를 씌우면 일반 인덱스를 못 탄다. 대용량이면 컬럼은 그대로 두고 비교 대상에만 함수를 적용하는 범위 조건으로 바꾸거나, 함수 기반 인덱스를 검토한다.

Q5. TO_DATE에서 ORA-01861 에러가 납니다.

입력 값과 포맷 문자열이 안 맞을 때 난다. 값이 2024/03/15면 포맷도 'YYYY/MM/DD'로 맞춘다. 값의 실제 구분자와 자릿수를 그대로 반영한다.

정리

Oracle 날짜 비교의 출발점은 DATE 타입이 시분초까지 저장한다는 사실이다. 그래서 특정 날짜를 =로 비교하면 거의 안 맞고, 하루 전체는 >= 그날 AND < 다음날 범위로 잡는 게 가장 안전하다. BETWEEN은 끝 날짜의 00:00:00 함정을 기억하고, 문자열과 DATE는 타입을 맞춰 비교한다. TRUNC는 편하지만 컬럼에 씌우면 인덱스를 못 타므로 대용량에서는 범위 조건을 우선한다. 실제 동작은 NLS 설정, 컬럼 타입, 인덱스 구성에 따라 달라질 수 있으니, 헷갈릴 때는 실행계획과 TO_CHAR 출력으로 실제 값을 직접 확인하는 것이 가장 빠르다.