How would I validate a string in the format YYYYMMDD is a valid date? I assume the range of valid years is 1940 to 2050.
Solved
Validate a string in the format YYYYMMDD
- Universe Traveller
- 9 replies
Best answer by may_kwok
Pasting the screenshot here for easier viewing:
The substr expression in Ataccama takes the first digit as position 0.
So, if you have string = ‘20250130’, your expression substr(field_4,1,4) will evaluate to ‘20250130’ instead of ‘20250130’.
If you wanted the first 4 chars and you wanted to use the substr expression, then it needs to be substr(field_4,0,4).
If you wanted to allow nulls, you can edit the rule so that:
Condition 1 is that if it is empty, it is valid (instead of invalid). Then,
Condition 2 is make sure the string can convert to a date. Then,
Condition 3 is the year is within range.
Then you can avoid all the logic like “month cannot be greater than 12”, 28 / 29 / 31 days, which can just be validated against the calendar.
Does this help?
+4- Star Blazer L3
- 82 replies
I’d approach it first by making sure the string is actually a date, and then picking out the year component from it and testing whether it is earlier or later than the range.
So first, in the first condition I use:
toDate(date_string,'yyyyMMdd') is nullIf the string is not a valid date, it will fail to convert and return null. Note that Ataccama expression language uses lower case yyyy for year and lower case dd for date.
Then, I take the first 4 chars of the text, to determine if it is smaller than 1940 or larger than 2050.
left(date_string,4)<'1940'
or left(date_string,4)>'2050'This approach will be able to weed out any random chars that are not dates (the abcdefgh), and also date formats that are wrong (the 20252901 or 29012025)
Does this help?
- Universe Traveller
- 9 replies
I like your approach and will give it a try.
But let me share that I approached the problem by using subStr to separate the value into variables YYYY, MM and DD. I actually did pretty well, but I could not get (YYYY < “1940”) or (YYYY > “2050”) to return correct answers, even if I split it into two separate conditions. Would you know why (YYYY < “1940”) would not work?
Thanks for your time!
+4- Star Blazer L3
- 82 replies
Hi
Could you give a screenshot similar to the one that I pasted? This way we can see all the configurations and usage of expressions?
+4- Star Blazer L3
- 82 replies
- Answer
Pasting the screenshot here for easier viewing:
The substr expression in Ataccama takes the first digit as position 0.
So, if you have string = ‘20250130’, your expression substr(field_4,1,4) will evaluate to ‘20250130’ instead of ‘20250130’.
If you wanted the first 4 chars and you wanted to use the substr expression, then it needs to be substr(field_4,0,4).
If you wanted to allow nulls, you can edit the rule so that:
Condition 1 is that if it is empty, it is valid (instead of invalid). Then,
Condition 2 is make sure the string can convert to a date. Then,
Condition 3 is the year is within range.
Then you can avoid all the logic like “month cannot be greater than 12”, 28 / 29 / 31 days, which can just be validated against the calendar.
Does this help?
- Universe Traveller
- 9 replies
Hi
I went back to implement the technique in your first recommendation using toDate and I was surprised when I could not get it to work. Here are my details. What did I miss?
By the way, is there any way to clean up the fields which I deleted?
Thanks!
Tim
Reply
Most Liked this week
Login to the Ataccama Community
No account yet? Create an account
For Ataccama Customers and Partners
or
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.