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.

Answer

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