How to identify DateTime conversion errors when retrieving from a database?

My errors are because of dates like "0022-05-01 00:00:00" and "0217-12-07 00:00:00"

Database is ~800k records, I get ~10 such errors. But when I browse the data - before any processing at all - I can't find those exact entries that are causing the errors. Is Alteryx replacing them with null? Is it dropping the entire record?

How can I get the data "raw" like tell Alteryx to just take it as a string don't even try to convert it? Will I have to do SQL queries for all the error-inducing dates to identify the records?

Date Time

Data Investigation

Accepted answers

j_hoyt

Solved: I used an SQL Query something like:

SELECT *, cast(sketchy_date AS varchar) as sketchy_date_raw

FROM table

WHERE(DatePart(yy, sketchy_date) < 1000)

And I got all my offending records with NULL where the field conversion errors happened, and the bad dates as varchars in a new column. Alteryx dropped the original values but happily accepted and retained the bad dates as strings.

All comments

Watermark

If you think the format error has a consistent pattern, you could use a regex match formula to match on the error pattern (returns true/false) and then filter for those that come up matching (true) to your pattern. Partial example is the 2 examples above both start with a '0'. I don't know if you know that's the problem or if that's just an example. Or if you're not sure of what the error is, you could demand the match is equal to the 'correct' formatting, and anything that doesn't match the correct format is then spit out as false.

j_hoyt

All the bad dates have a year that starts with 0 so a simple regex would catch them all. My problem is that the bad data is identified when I retrieve the database (Field conversion errors that tell me each bad date) but then I browse/filter the data and I can't find them anywhere.

Literally the flow is "DATA IN" --> "BROWSE" and none of the bad dates are found in the Browse block so a regex isn't going to catch them after that.

My guess is that Alteryx either puts in a NULL for the field conversion error (likely) or just drops the record (unlikely). But those dates are like 30% null so they would just get lost in the mix.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703