Skip to main content
Solved

Date Field Format Data Quality Check

  • November 8, 2024
  • 7 replies
  • 127 views

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

​

View original

anna.spakova
Ataccamer
Forum|alt.badge.img+3

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


Cansu
Community Manager
Forum|alt.badge.img+3
  • Community Manager
  • January 2, 2025

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πŸ™‹πŸ»β€β™€οΈ


Forum|alt.badge.img

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

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 ?


anna.spakova
Ataccamer
Forum|alt.badge.img+3

​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

​


Thanks Anna.


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings