Skip to main content
Solved

Date Field Format Data Quality Check

  • November 8, 2024
  • 7 replies
  • 164 views
E
Universe Traveller
Forum|alt.badge.img

Hey y’all,

 

I was wondering if anyone had a quality rule or advice on how to construct a quality rule to check a date type field for formatting. I know there is the “matches” function for strings and that you can convert a date field to a string using “toString”, but that requires you to specify a format, which invalidates the point of the rule.

The desired format is “MM-dd-yyyy”, as shown in what I’ve got so far.

matches("^\d{4}-(0[1-9]|1[0-2])-(0[1-9]|[12][0-9]|3[01])$",toString(ATTRIBUTE_1, "MM-dd-yyyy"))

Best answer by anna.spakova

​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}-(0[1-9]|1[0-2])-(0[1-9]|[12]\d|3[01])",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

​

7 replies

anna.spakova
Ataccamer
Forum|alt.badge.img+3
  • anna.spakovaAtaccamer
  • Ataccamer
  • 211 replies
  • November 20, 2024

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

A
Forum|alt.badge.img+1

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🙋🏻‍♀️

Check out our Quick Start Guide to get started on the community 🙋‍♀️
E
Universe Traveller
Forum|alt.badge.img
  • evan.sasowskyUniverse Traveller
  • Author
  • Universe Traveller
  • 4 replies
  • January 10, 2025

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?

anna.spakova
Ataccamer
Forum|alt.badge.img+3
  • anna.spakovaAtaccamer
  • Ataccamer
  • 211 replies
  • January 22, 2025

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

S
Space Explorer
  • Shefhali singhSpace Explorer
  • Space Explorer
  • 4 replies
  • June 20, 2025

what if the datatype is string, then how to validate the date ?

anna.spakova
Ataccamer
Forum|alt.badge.img+3
  • anna.spakovaAtaccamer
  • Ataccamer
  • 211 replies
  • Answer
  • June 20, 2025

​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}-(0[1-9]|1[0-2])-(0[1-9]|[12]\d|3[01])",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

​

S
Space Explorer
  • Shefhali singhSpace Explorer
  • Space Explorer
  • 4 replies
  • June 27, 2025

Thanks Anna.


Community Terms of Use & ConditionsCookie settingsAccessibility statement