presto SQL - Filter records if date column format is not matching yyyy-MM-dd

Question

I need to get all the records from a table that has a date not matching with the format yyyy-MM-dd.

Here, column ID is unique bigint column. start_date is of varchar datatype.

Sample input:

Expected output:

Thanks

Answer 1

Use regexp_like:

select id, start_date
  from mytable
 where NOT regexp_like(start_date, '\d{4}-\d{2}-\d{2}')

This will work for '11-12-200' and 'None'.

If you want to include NULL values as well, add additional condition:

 where (NOT regexp_like(start_date, '\d{4}-\d{2}-\d{2}'))
    OR start_date is null

More strict date regexp is '^\d{4}\-(0[1-9]|1[012])\-(0[1-9]|[12][0-9]|3[01])$'

This will restrict month to 01 .. 12 and day to 01 .. 31 and will not allow other characters before and after date( ^ and $ anchors are used).

One more simple and powerful method

is to use try_cast(col as date) - it will return NULL if not possible to cast:

where try_cast(start_date as date) is not null

This will also restrict wrong dates like Feb 30 (2000-02-30)