[SQL] 오라클 날짜 검색 방법 총 정리

SELECT * FROM 테이블명 
WHERE REGDATE BETWEEN #{startDate} AND #{endDate};

 조회가 안되서 찾아보니 여기에 문제점이 있다는 것을 확인했다.

 

REGDATE의 자료형은 DATE이다.

자료형을 맞춰주기위해 DATE자료형을 문자열로 바꾸거나,

문자열을 DATE자료형으로 형변환 시켜주어야 한다.

 

---

 

DATE자료형[컬럼]을 문자열로 변경하여 검색하는 방법 (권장x)

SELECT * FROM 테이블명 
WHERE 
TO_CHAR(REGDATE, 'YYYYMMDD') >= #{startDate} AND
TO_CHAR(REGDATE, 'YYYYMMDD' <= #{endDate};

비교연산자로도 동일한 결과가 검색되지만 특수한 경우가 아니면 BETWEEN연산자를 사용할 것을 권장.

SELECT * FROM 테이블명 
WHERE TO_CHAR(REGDATE, 'YYYYMMDD') BETWEEN #{startDate} AND #{endDate};

해당 방법은 권장하지 않으며 성능이 떨어진다고 한다.

기존 컬럼 데이터 타입은 살리는 것이 좋다.

 

 

문자열[입력값]을 DATE자료형으로 변경하여 검색하는 방법

SELECT * FROM 테이블명 
WHERE REGDATE BETWEEN TO_DATE(#{startDate}, 'YY/MM/DD') AND TO_DATE(#{endDate}, 'YY/MM/DD');

 

 

위와 같은 형태로 날짜 검색을 했을 경우,

예를 들어 startDate가 20230301 이고, endDate가 20230330 일 경우

종료값인 2023년 3월 30일의 데이터가 포함되지 않을 수 있다.

끝 값은 2023-03-30 00:00:00 의 타임스탬프 값을 나타내서,

날짜는 같지만 자정 이후인 타임스탬프 값이 있는 레코드는 포함되지않는다.

 

타임스탬프 값이 2023년 3월 30일인 모든 레코드가 결과 집합에 포함되도록 하기 위해

날짜 범위 끝에 값 0.99999을 추가하면 (이것은 자정부터 하루가 끝날때까지 경과한 하루의 비율을 나타내며 

약 23시간 59분 59.999초) 타임스탬프 값이 2023년 3월 30일에 해당하는 모든 레코드가 결과 집합에 포함된다.

 

SELECT * FROM 테이블명
REGDATE BETWEEN TO_DATE(#{startDate}, 'YY/MM/DD') AND TO_DATE(#{endDate}, 'YY/MM/DD')+0.99999;

 

---

 

지정한 날짜와 동일한 데이터 검색하는 방법

SELECT * FROM 테이블명
WHERE TRUNC(REGDATE) = to_date('20230330', 'YY/MM/DD')

이 쿼리는 TRUNC 함수를 사용하여 "REGDATE" 열의 타임스탬프 구성 요소를 자르고 날짜 값으로 변환한다.

그런 다음 결과 날짜 값을 "to_date" 함수를 사용하여 'YY/MM/DD' 형식의 문자열 '20230330'으로 표시되는 날짜 값과 비교합니다.

 

아래의 쿼리문과 비교했을때 TRUNC함수를 사용한 위와 같은 쿼리가 더 성능이 좋다.

위 쿼리는 상대적으로 가벼운 작업인 날짜 자르기 및 변환만 수행하는 반면

아래 쿼리는 계산 비용이 더 많이 들 수 있는 문자열 변환을 수행하기 때문이다.

SELECT * FROM 테이블명
WHERE TO_CHAR(REGDATE, 'YYYY-MM-DD') = '2023-03-30'