I'm trying to get all the purchase date data from a table but it spits out an error because there's an invalid date on the table. I have tried looking around for similar problems, but the problem I have seems to be a bit different. My problem is there is a date where the year value is invalid, it's: 650-01-22

Error running query Invalid date: '650-01-22'; while executing the filter on column 'output.purchase_date.value'; Column 'request_id'

I have tried putting a filter to only produce data over a certain date, casting it into a string, safe casting, re-parsing the date format, but I keep encountering the same error

SELECT purchase_date FROM MyTable WHERE purchase_date >= "2020-08-10"

Any idea if this is possible without messing with the table directly? Is it possible to just filter them out? Thank you!

CodePudding user response：

According to docs date type is valid from 0001-01-01 to 9999-12-31, my test showed that date should be 0650-01-22 with the left missed zero instead of 650-01-22. Apparently there is no way for query that date column without a hand for ajustments in your data.