[SQL] Oracle 함수 TO_CHAR와 TO_DATE 간단 비교 및 날짜포맷 정리

두 함수는 실무에서 오라클 쿼리 사용 시 날짜 비교 또는 날짜 조건 조회할 때 자주 사용하는 함수이다.

TO_CHAR(날짜타입값, '날짜포맷')
날짜타입의 데이터를 '날짜포맷'에 따라 문자열로 변환

SELECT
	TO_CHAR(SYSDATE, 'YYYY-MM-DD') -- '2023-05-18'
FROM DUAL;

TO_DATE('날짜문자열', '날짜포맷')
'날짜포맷'에 맞춘 날짜문자열을 날짜타입값으로 변환

SELECT
	TO_DATE('20230518', 'YYYYMMDD') -- '2023-05-18 00:00:00.000'
FROM DUAL;

오라클 날짜 포맷은 다음과 같다.

형식	설명
YYYY	네 자리 연도(예: 2023)
YY	연도의 마지막 두 자리(예: 23)
MM	두 자리 월(예: 5월의 경우 05)
MON	월 약칭(예: May)
MONTH	전체 월 이름(예: May)
DD	두 자리 날짜(예: 18)
DY	요일의 약어(예: Mon)
DAY	전체 요일(예: 월요일)
HH24	24시간 형식의 두 자리 시(예: 오후 5시인 경우 17)
HH	12시간 형식의 두 자리 시간(예: 05 PM)
MI	두 자리 분(예: 30)
SS	두 자리 초(예: 45)
AM 또는 PM	12시간 형식의 자오선 표시기(예: AM)

이러한 서식 요소를 대시, 슬래시, 콜론 및 공백과 같은 구분 기호와 결합하여 다양한 날짜 서식을 만들 수 있다.

'YYYY-MM-DD' 2023-05-18
'YYYY MON DAY' 2023 5월 금요일
'YYYY.MM.DD HH24:MI:SS' 2023.05.18 17:30:45
'YY/MM/DD HH:MI AM' 23/05/18 05:30 오전

참고로 오라클에서 날짜포맷은 대소문자를 구분하지 않는다.

추가적으로 활용 예제를 공유한다.

날짜를 의미하는 문자열의 데이터를 파라미터로 받는다고 가정할때
어떤 회원테이블에서 23년 5월 18일에 가입한 회원의 목록을 조회한다면?

SELECT
	USER_ID,
    FRST_REG_DT -- 최초등록일시
FROM 
	T_MEMBER
WHERE 
	TO_CHAR(FRST_REG_DT, 'YYYYMMDD') = '20230518'

DATE 타입의 컬럼인 FRST_REG_DT를 'TO_CHAR'함수를 사용해 문자열로 치환하여 요청 데이터로 받은 문자열과 비교 할 수 있을 것이다.

하지만 전국민의 정보가 저장된 대용량의 회원테이블이라면?

SELECT
	USER_ID,
    FRST_REG_DT -- 최초등록일시
FROM 
	T_MEMBER
WHERE 
	FRST_REG_DT LIKE TO_DATE('20230518', 'YYYYMMDD')

다음과 같이 작성하는 쿼리가 성능에 훨씬 유리할 것이다. 즉, TO_DATE 함수를 사용해 조건컬럼의 타입을 변경하지 않고 파리미터의 문자열을 변형하여 검색속도를 향상 시킬 수 있으며 만약 'FRST_REG_DT'가 인덱스 컬럼이라면 인덱스 사용이 가능해진다.

추가적으로 주의할점❗❗
조건절에서 TO_DATE() 함수를 포맷 문자열없이 사용할때 개발환경에서는 문제가 되지 않지만 배포 환경에 따라 타입이 맞지 않아 오류를 발생할 수 있다. 즉 끔찍한 일이 벌어질것... 포맷 문자열은 반드시 넣어주자.

• TO_DATE('20230518') ❌
• TO_DATE('20230518', 'YYYYMMDD') ⭕