[Oracle] 날짜형식(YYYY-MM-DD) 치환하기 - 정규식, Formatting, ORA-01481, ORA-01861

 

 

날짜형태의 데이터를 문자열(VARCHAR)필드로 관리할 때,

날짜포맷(YYYY-MM-DD)로 치환하여 조회하면서 발생하는 이슈들에 대한 해결법을 정리한다.

1. TO_DATE, TO_CHAR

[코드]

-- STR_DT : VARCHAR(8)
SELECT STR_DT, TO_DATE(STR_DT, 'YYYY-MM-DD') FROM TMP_TEST_01

-- [72000][1481] ORA-01481: invalid number format model
SELECT STR_DT, TO_CHAR(STR_DT, 'YYYY-MM-DD') FROM TMP_TEST_01

-- TEMP_DT : DATE
SELECT TEMP_DT, TO_CHAR(TEMP_DT, 'YYYY-MM-DD') FROM TMP_TEST_01

[결과]

 1) 'STR_DT'는 VARCHAR형태 데이터로, TO_DATE를 통해 날짜형치환으로 포맷팅할 수 있다.

 2) 해당 데이터가 이미 문자열(VARCHAR)이므로 TO_CHAR형태의 치환은 아래 오류가 발생한다.

[72000][1481] ORA-01481: invalid number format model

 3) 'TEMP_DT'는 DATE형태의 필드 데이터로 문자형변환(TO_CHAR)을 통해 날짜포맷을 맞출 수 있다.

단, TO_DATE의 경우 형변환을 할 때 "적합한 숫자(YYYYMMDD)가 들어야 한다는 문제점"이 있다. 이를 위반한 데이터가 존재하는 경우 아래와 같은 오류가 발생한다.
(예를 들어, '20220522' 형태 데이터가 아닌 '202205' 같은 형태의 데이터)

[22008][1861] ORA-01861: literal does not match format string

소스구조상 예외처리가 잘 되어있다면 몰라도, 데이터를 저장하는 데에 제약을 두지 않은 상황이라면 곤란한 상황이다.

따라서, 아래와 같이 정규식을 통한 조회 쿼리를 사용하고자 한다.

2. 정규식 표현

[코드]

SELECT STR_DT, REGEXP_REPLACE(STR_DT, '(.{4})(.{2})(.{2})', '\1-\2-\3') AS STR_DT FROM TMP_TEST_01

[결과]

 결과와 같이 문자열필드에 한해서, 정규식에 부합하는 경우 목표했던 YYYY-MM-DD를 출력하고 그 외에는 문자열 그대로를 출력한다.