DSXchange

There is a downloadable DSX file here

Nice.

In production this function will not be optimally efficient, since it needs to go through the CASE construct for every row. A much improved version will appear shortly - I'll advise on this thread, and document why the new version is, to quote Craig H, "more better".

Excellent!

More better is always gooder than less better. :D

Here is a part of IsValid function which checking if a date is valid:

Date:
* Date in current locale format or in ISO 8601 format (YYYY-MM-DD)
Test1 = Iconv(argTestValue, "D") ; * current locale
Status1 = Status()
Test2 = Iconv(argTestValue, "DYMD")
Status2 = Status()
Ans = (Status1 = 0 Or Status2 = 0)
RETURN(Ans)

Once I already tried the same way for checking date validity, but it didn't work.
Try the string "00222". Iconv with "D" format returns "09 Aug 2000", format "DYMD" returns "2000-8-09" and Status = 0.
One more and stranger case: the string "0?002".
Format "DYMD" returns "2000-2-01".
So it can't catch a real junk...

These are not junk. The rules of DataStage specify that a five or seven digit number will be interpreted as a two- or four-digit year followed by a three-digit ordinal number of the day in the year. Therefore, 00222 is the 222nd day of the year 00 which, if your CENTURYPIVOT configuration parameter is set to its default value, is the year 2000. The 222nd day of a leap year is August 9th.

But how DS convert "00222" to date "09 Aug 2000"?
And how DS interprets "?" in the string "0?002"?

I explained how the conversion is effected in my previous post on this thread.

The "?" character is interpreted as a delimiter character, then the rules that I outlined are applied.

Premium membership costs less than 30c per day and allows you, among other things, to read the entirety of posts by the five premium posters. All the revenue from premium memberships is devoted to defraying the hosting and bandwidth costs incurred by DSXchange.

Your explanation didn't explain how "00222" converted to date "09 Aug 2000"

Yes it did.

Since you haven't purchased premium membership you are unable to read the entire reply.

So as your mind strings "00222" and "0?002" are a valid dates?
No comments...

Yes they are, according to DataStage's rules.

And what about elementary logic?
Oracle, for example, can't convert this string to date by to_date function and sends it to exceptions. That is a right action because "0?002" or "00222" are junk but never valid date

Just because Oracle does it one way does not make it a standard. Here's a hint. 00222 is a valid date, so is 00365, but 00367 is not.

Don't let Larry hear you say that!