CODEWITHSUNDEEP
sqlpostgresqlcase
kevidigi
kevidigi

Reputation: 3

Filtering null values when casting - using CASE statements

I've been trying to query rows with a date field up to a threshold. For reasons beyond my control, all fields are varchar. I'm trying to filter out null values which case an error when cast. My latest attempt:

SELECT *
FROM a_table AS t
WHERE (
    CASE
        when t.dateField is not null then cast(t.datefield as date)
        else cast('2000-01-01' as date)
    END
) <= cast('2017-08-19' as date) ;

The above code still hits an error trying to cast "" to a date.

I thought this would be a common problem but I can't seem to find an idiom to achieve this, which makes me think I'm barking up the wrong tree. Can anyone point me to the correct one?

EDIT: Where entries are not null, they have been entered as DD/MM/YYYY.

Upvotes: 0

Views: 1282

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You can use coalesce():

SELECT *
FROM a_table AS t
WHERE coalesce(t.dateField, '2000-01-01') <= '2017-08-19'::date;

I'm not sure why you would want to coalesce a "dateField" column to a date. Seems like it already should be a date.

You can also express this as:

WHERE t.dateField <= '2017-09-19' OR t.dateField IS NULL

If you want to convert a column in the format DD/MM/YYYY to a date, then use to_date():

WHERE to_date(t.dateField, 'DD/MM/YYYY') <= '2017-09-19' OR t.dateField IS NULL

Upvotes: 1

Vitaliy Smolyakov
Vitaliy Smolyakov

Reputation: 472

As proposed by GordonLinoff you can skip not valid values from casting

SELECT *
FROM a_table AS t
WHERE t.dateField IS NULL 
  OR dateField = '' 
  OR cast(t.datefield as date) < cast('19/08/2017' as date)

Upvotes: 0

Related Questions