[해결 방법] ORA-01861: literal does not match format string

👉 기본 환경

- Language: Oracle

 

 

⌨️ 코드

   SELECT B.TITLE, 
         B.BOARD_ID, 
         R.REPLY_ID, 
         R.WRITER_ID, 
         R.CONTENTS, 
         TO_CHAR(R.CREATED_DATE, 'YYYY-MM-DD') CREATED_DATE
    FROM USED_GOODS_BOARD B 
    JOIN USED_GOODS_REPLY R 
      ON B.BOARD_ID = R.BOARD_ID
   WHERE B.CREATED_DATE >= '2022-10-01'
     AND B.CREATED_DATE < '2022-11-01'
ORDER BY R.CREATED_DATE, B.TITLE
;
 

 

 

🖨️오류

ORA-01861: literal does not match format string

 

 

📡 원인

DATE 데이터 유형이과 문자열의 비교

Oracle에서는 DATE 형식의 날짜와 문자열 간의 직접적인 비교를 허용하지 않음

 

 

📰 해결 방법

  SELECT B.TITLE, 
         B.BOARD_ID, 
         R.REPLY_ID, 
         R.WRITER_ID, 
         R.CONTENTS, 
         TO_CHAR(R.CREATED_DATE, 'YYYY-MM-DD') CREATED_DATE
    FROM USED_GOODS_BOARD B 
    JOIN USED_GOODS_REPLY R 
      ON B.BOARD_ID = R.BOARD_ID
   WHERE B.CREATED_DATE >= DATE '2022-10-01'
     AND B.CREATED_DATE < DATE '2022-11-01'
ORDER BY R.CREATED_DATE, B.TITLE
;
 

DATE 타입임을 명시

 

🚨 DATE 타입 사용 시, 'YYYY-MM-DD' 형태로만 사용 가능

  SELECT B.TITLE, 
         B.BOARD_ID, 
         R.REPLY_ID, 
         R.WRITER_ID, 
         R.CONTENTS, 
         TO_CHAR(R.CREATED_DATE, 'YYYY-MM-DD') CREATED_DATE
    FROM USED_GOODS_BOARD B 
    JOIN USED_GOODS_REPLY R 
      ON B.BOARD_ID = R.BOARD_ID
   WHERE B.CREATED_DATE >= DATE '2022-10'
ORDER BY R.CREATED_DATE, B.TITLE
;
 

사용 시, ORA-01861: literal does not match format string 오류 발생