Hi @evan.sasowsky,
let me just comment regarding the data type. In case your date is stored in a date or datetime field, it doesn’t make sense to perform a format check because the formatting is based on the settings of your database and Ataccama has also some specific format how it displays the dates in the web application. But it is all just the visualization of the dates. So the format check makes sense only in case the dates are stored in string data types.
Kind regards,
Anna
Hi @evan.sasowsky, I’m closing this thread for now, if you have any follow-up questions please feel free to share them in the comments or create a new post
♀️
Hey @anna.spakova,
That makes sense! I appreciate the explanation.
I guess the only exception to a specified format would be if you were bringing in a csv or and xlsx file. Would Ataccama just assume a string format for those?
Hi @evan.sasowsky, yes, CSV and Excel files import all fields as STRING so in those cases it makes sense to have the date format check.
Kind regards,
Anna
what if the datatype is string, then how to validate the date ?
Hi @Shefhali singh
you have multiple options, you can use some regex function - eg function matches() - if the string matches the pattern, the result is TRUE, otherwise it’s FALSE. Alternatively, you can try to transfer the data type to date (or datetime) using function toDate() or toDatetime() - if the result is not null, it has the expected format, if the result is null, the function was not able to convert it to date. You can use that with a combination with iif() function, since the DQ rule condition in the web application expects the result to be boolean (true or false).
For examples:
matches(@"\d{4}-(0e1-9]|1{0-2])-(091-9]|-12]\d|3101])",ATTRIBUTE_1)
iif(toDate(ATTRIBUTE_1,'yyyy-MM-dd') is not null,false,true) (false for satisfying the condition because the best practice is to use the condition for invalidity.
Please let me know if this is clear.
Kind regards,
Anna
