How to validate invalid Date format in Appian for Date columns

Question

Hi All, 
 
 We have a requirement, where we have to import excel file data into Appian database stage table. Once data is loaded into stage table we have to validate the date format for date columns whether the date is invalid or valid using query entity or any custom function. In-valid records should be removed and only valid records be there. Can someone help us with this kind of scenario in Appian how we can avoid the in-valid date rows in Appian. 
 
 Regards, 
 Dheeraj G

Abhishek Karumuru · Accepted Answer

hi dheerajg0003 you can have some SP to check and update your date format after once pushing the data into a staging table. example code snippet: 
 UPDATE staging_table
SET is_valid_date = CASE 
 WHEN STR_TO_DATE(date_column, '%Y-%m-%d') IS NOT NULL THEN 1
 ELSE 0
END;

Mathieu Drouin · Answer

Another method that does not require a stored proc would simply to have a process that reads the staging table in batches and then either updates the staging table or inserts the valid rows into another table.

How to validate invalid Date format in Appian for Date columns

Top Replies