Date parsing issue in the where clause in Snowflake query

Question

I am running a Snowflake query that is intended to return records that among other things, have a date earlier than the current date.

select *
from table_a
where id < 100 and
date < ???

The problem is, the data this query is running on has issues in the date column. For example, one value may be a single digit (like 2). I don't want the query to break on those rows. That's why I'm looking to write a case in the where clause to only consider records that have their date as YYYY-MM-DD. I tried the following, but it is returning syntax errors:

and date = case WHEN date like '____-__-__' then date < current_date() else NULL

Also

and date < case WHEN date like '____-__-__' then current_date() else NULL

(In case it's not visible due to formatting, that is 4 underscores - meaning any character, followed by one dash, followed by 2 underscores, then a dash, and then another two underscores.)

You can assume current_date() has no issues. It can even be replaced with some hard-coded value if needed.

How can I write this query?

Answer 1

Use TRY_TO_DATE(date,'YYYY-MM-DD') as it tries to converts it to date and when invalid format it will turn automatically to NULL

So, you can apply a filter is not NULL beside the date comparison.

https://docs.snowflake.com/en/sql-reference/functions/try_to_date.html