일부 값이 null인 경우 Oracle에서 날짜를 비교하시겠습니까? (Compare date in Oracle when some value is null?)

문제 설명

일부 값이 null인 경우 Oracle에서 날짜를 비교하시겠습니까? (Compare date in Oracle when some value is null?)

만료일자가 "2015년 31월 12일" 미만인 레코드를 가져오고 싶지만 데이터베이스의 일부 만료일 필드 값이 null입니다. 이것은 내 SQL입니다:

"AND TO_DATE(EXPIRED_DATE,'DD/MM/YYYY') < TO_DATE('31/12/2015','DD/MM/YYYY')"

오류가 있습니다

"day of the month must between 1 and the last day of the month" 

expired_date <'31/12/2015'?

편집: 문제는 null 값이 아니라 내 DB의 형식입니다. "22‑APR‑15"를 저장하고 실수로 to_date 함수로 'DD/MM/YYYY'에 할당하려고 합니다.

참조 솔루션

방법 1:

If your column EXPIRED_DATE is of date type then, you don't need to convert it to date again using TO_DATE.

I think you need the following:

AND EXPIRED_DATE < TO_DATE('31/12/2015','DD/MM/YYYY')

This will return false for any EXPIRED_DATE which is null and that record will not be included in the result.

If you want Null EXPIRED_DATE to be included in the result then you can use OR as following:

AND (EXPIRED_DATE IS NULL OR EXPIRED_DATE < TO_DATE('31/12/2015','DD/MM/YYYY'))

방법 2:

Your code ...E(EXPIRED_DATE,'DD/MM/YYY')... has 3 YYY for date instead of 4 YYYY. Tried below and it works

with da(date_a) as (
    select '03/04/2015' from dual
    union all select '03/04/2015' from dual
    union all select '03/04/2017' from dual
    union all select '03/04/2015' from dual
    union all select '03/04/2016' from dual
    union all select NULL from dual
)
SELECT * FROM da WHERE   TO_DATE(date_a,'DD/MM/YYYY') < TO_DATE('31/12/2015', 'DD/MM/YYYY');

Even when your date column is in date format it will still work

with da(date_a) as (
    select '03/04/2015' from dual
    union all select '03/04/2015' from dual
    union all select '03/04/2017' from dual
    union all select '03/04/2015' from dual
    union all select '03/04/2016' from dual
    union all select NULL from dual
)
SELECT to_date(date_a, 'DD/MM/YYYY')  date_a  FROM da WHERE   TO_DATE(date_a,'DD/MM/YYYY') < TO_DATE('31/12/2015', 'DD/MM/YYYY');

db<>fiddle

(by NAM、Popeye、Omari Victor Omosa)

참조 문서

1. Compare date in Oracle when some value is null? (CC BY‑SA 2.5/3.0/4.0)