[Oracle] 오라클 문자열 날짜 형식 체크 방법 (IsDate)
- 데이터베이스/오라클
- 2021. 7. 8.
오라클에서 문자열의 날짜를 TO_DATE 함수를 사용하여 날짜 형식으로 변경할 때 형식이 맞지 않으면 "ORA-01839: 지정된 월에 대한 날짜가 부적합합니다" 라는 오류가 발생한다.
문자열을 날짜 형식으로 변환하기 전에 미리 날짜 형식이 맞는지 체크를 한 후 변환을 해야 오류 발생을 방지할 수 있다. 오라클 12c R2부터는 형식 체크 함수가 생겨서 쉽게 체크할 수 있지만, 이하 버전에서는 형식 체크 방법을 직접 구현해야 한다.
문자열 날짜 형식 체크 방법 3가지
|
VALIDATE_CONVERSION 함수를 사용하는 방법 (오라클 12c R2 이상)
SELECT VALIDATE_CONVERSION('2021-07-08' AS DATE, 'YYYY-MM-DD')
FROM dual
 |
날짜 형식 맞으면 "1", 틀리면 "0"을 반환한다.
(VALIDATE_CONVERSION : 오라클 형식 체크 정식 함수)
조건절(WHERE)에 사용하는 방법
WITH emp AS (
SELECT 'SCOTT' AS ename, '1987-04-19' AS hiredate FROM dual UNION ALL
SELECT 'CLARK' AS ename, '1981-06-31' AS hiredate FROM dual
)
SELECT *
FROM emp
WHERE VALIDATE_CONVERSION(hiredate AS DATE, 'YYYY-MM-DD') = 1
 |
VALIDATE_CONVERSION 함수를 조건절에 사용하면 날짜 형식이 맞거나 틀린 데이터를 조회할 수 있다.
날짜 형식이 틀린 경우
 |
IS_DATE 사용자 정의 함수를 생성하여 사용하는 방법 (모든 버전)
CREATE FUNCTION is_date(p_str VARCHAR2, p_format VARCHAR2)
RETURN NUMBER
IS
v_date DATE;
BEGIN
v_date := TO_DATE(p_str, p_format);
RETURN 1;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END;
IS_DATE 사용자 정의 함수를 생성 후 쿼리문에서 사용한다.
SELECT is_date('2021-06-31', 'YYYY-MM-DD')
, is_date('2021-07-08', 'YYYY-MM-DD')
FROM dual
 |
VALIDATE_CONVERSION 함수와 동일한 결과를 반환한다.
REGEXP_INSTR 정규식 함수를 사용하는 방법 (오라클 10g 이상)
SELECT REGEXP_INSTR('2021-07-08', '^\d{4}-\d{2}-\d{2}')
FROM dual
 |
문자열의 형식이 날짜 형태(0000-00-00)가 맞는지만 체크가 가능하여, 날짜 값 자체의 유효성 체크는 하지 못한다.
간단한 날짜 형태만 체크할 때 사용하면 좋을 듯하며 정확한 체크는 VALIDATE_CONVERSION 함수를 사용하거나, 사용자 정의 함수를 생성하여 사용하는 것이 바람직하다.
SELECT REGEXP_INSTR('2021-07-08', '^((19|20)\d{2})-(0[1-9]|1[012])-(0[1-9]|[12][0-9]|3[01])')
FROM dual
 |
정규식 기능을 조금 더 보완하여 날짜 형식을 체크하는 방법이다.
(19|20)\d{2}) : 19 또는 20으로 시작하는 4자리
(0[1-9]|1[012]) : 01~09, 10, 11, 12 허용
(0[1-9]|[12][0-9]|3[01]) : 01~09, 10~29, 30, 31 허용
날짜의 월, 일에 사용 가능한 숫자인지 체크할 수 있도록 기능은 보완되었지만, 해당 월의 마지막 일이 28일, 30일, 31일, 29일(윤달)인지 등은 체크하지 못한다.
정규식의 기능을 조금 더 추가하면 정교한 체크가 가능하겠지만, 수식이 길어지면 쿼리 문의 가독성이 떨어지므로 상황에 맞게 사용하는 것이 바람직하다.