Retrieve data with bad date format - Snowflake-CodePudding

A json data seems to have a bad date format, eg - '2019-07'. The correct date format would look something like this, eg - '2019-12-30T03:47:23.962'

I am trying to retrieve the whole record which has this bad data. I am using variations of the following query, but nothing seems to be working -

eg - retrieves all the files which has the below dates in it, as expected

SELECT * FROM data where data.date LIKE ' 19-07%';

eg2- does not retrieve any data

SELECT * FROM data where data.date LIKE '2019-07 %';

Any help is truly appreciated!

CodePudding user response：

Using Error-handling Conversion Functions - TRY_CAST:

Conversion functions with a TRY_ prefix are special versions of their respective conversion functions. These functions return a NULL value instead of raising an error when the conversion can not be performed

select * 
from data 
where try_cast(data:date::string as date) is null;