Test the date field is correct

Question

Hello,

i have various excel inputs coming into my workflow which i union into one dataset. Each file will have a date field but sometimes the formats will not be automatically set as date so need to be adjusted. I want a test on the dataset after the union to flag any rows where the date format is not correct. Is there a good way of doing this?

even one incorrect date format will cause issues later in the model.

vlad_kutateladze · Answer

If date type field will be union with string type as one field, the whole field will become a string, all rows will be the same and you can just apply DateTime tool for this field and convert string to date format.

DavidP · Answer

Hi @Sammy22

One very easy way to do this is to use a Select tool and change the datatype to Date - any rows with incorrect format will be converted to Null.

You can use the same idea in a formula tool and create a new field called datetest and use the formula Todate([Date]) - any incorrect formats will be converted to null.

If you want to follow a more complicated route, you can use a regex match formula like: regexmatch([Date],'\d{4}-\d{2}-\d{2})