Date Validation

[pkothana]

Hi,
I have a date field coming in the format MMDDYYYY. I have to check for it's validity i.e. whether it is a valid date or not and I have to check for the format of the date i.e. incoming date is in MMDDYYYY format or not.

What is the best way to do this validation?

Thanks & Regards
Pinkesh

[WoMaWil]

make a iconv and ask the status

[clshore]

Date parsing and validation can be expensive. If performance is important, an old trick from Server jobs is to pre-create a static reference hash whose whose keys are the set of all valid dates in your range of interest ( 01/01/1903-12/31/2034 for example ). Join your date field to the hash, then check for existence. If it doesn't exist, then it's either an invalid date or an invalid format. Either way, it requires some kind of exception handling.

Carter

[Teej]

iconv() is not possible for PX 6.0.

* * *

Okay, fortunately, you do not need a Custom/Build stage for this, however, you may want to consider it if you need to optimize the performance for extremely large data files.

You need approximately 2 stage variables. You may want more or less depending on what you want to do with the results.

Code: Select all

1.  StringToDate([input],"%mm%dd%yyyy")

This variable need to be defined as a Date field. Input is assumed to be a varchar.

Code: Select all

2.  If len([input]) <> 8 Then "This date is not the right length" Else If Compare([Variable #1], "**********") Then [Variable #1] Else "This date is not valid."

This will validate whether the string is the right length. StringToDate would happily convert something like "112019741" even though it have an extra digit. StringToDate does spit out the stars if it doesn't like the date format. Compare() spit out 0 if it's a perfect match. 0 is a 'fail" for the if statement.

This solution does not address whether "11011974" means "January 11, 1974" or "November 1, 1974". This solution does not address different calendar formats.

It is up to you what you want to do with the failures.

-T.J.

[ray.wurlod]

StringToDate would happily convert something like "112019741" even though it have an extra digit.

That's because November 20, 19741 is a valid date (if somewhat in the future!).

Basically you have to look at the month, which must be between '01' and '12', the day, which must be between '01' and 'nn', where nn varies between months and whether it's a leap year, and the year, which must be in a range that suits your business rules. You can do it all with string manipulation given that month and day are always two digits (that is, with leading zeroes).

[Teej]

That's because November 20, 19741 is a valid date (if somewhat in the future!).

Actually, StringToDate() would still convert it only to Nov 20, 1974, and promptly ignore the '1'.

Basically you have to look at the month, which must be between '01' and '12', the day, which must be between '01' and 'nn', where nn varies between months and whether it's a leap year, and the year, which must be in a range that suits your business rules. You can do it all with string manipulation given that month and day are always two digits (that is, with leading zeroes).

StringToDate() takes care of it, even the leap year rules (verified with 2000, and 2100). Yeah. I'm lazy. Sue me!

-T.J.

P.S. Message me if you want the test job.