CODEWITHSUNDEEP
oracle-database
NAM
NAM

Reputation: 147

Compare date in Oracle when some value is null?

I want to get records that have an expired_date less than "31/12/2015", but some value of expired_date field is null in the database. This is my SQL:

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

so I have got an error

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

How can I get just records that have an expired_date < '31/12/2015'?

EDIT: The problem is not the null value, but my format in my DB. I store "22-APR-15" and mistake is trying to assign it to 'DD/MM/YYYY' by to_date function

Upvotes: 1

Views: 1232

Answers (2)

Omari Victor Omosa
Omari Victor Omosa

Reputation: 2869

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

Upvotes: 0

Popeye
Popeye

Reputation: 35900

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'))

Upvotes: 1

Related Questions