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 null

If 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?

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!

Hi ​@TimBrown74 ,

Could you give a screenshot similar to the one that I pasted? This way we can see all the configurations and usage of expressions?

Please see attachment.

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?

Hi May,

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