IsValid Function

ray.wurlod · Post by **ray.wurlod** » Wed Jul 04, 2007 12:41 pm

There is a downloadable DSX file here

kduke · Post by **kduke** » Wed Jul 04, 2007 9:12 pm

Nice.

ray.wurlod · Post by **ray.wurlod** » Thu Jul 05, 2007 9:11 am

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".

chulett · Post by **chulett** » Thu Jul 05, 2007 9:29 am

Excellent!

More better is always gooder than less better. :D

admiral1969 · Post by **admiral1969** » Wed Mar 05, 2008 3:25 am

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...

ray.wurlod · Post by **ray.wurlod** » Wed Mar 05, 2008 3:57 am

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.

admiral1969 · Post by **admiral1969** » Wed Mar 05, 2008 4:11 am

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

ray.wurlod · Post by **ray.wurlod** » Wed Mar 05, 2008 6:01 am

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.

admiral1969 · Post by **admiral1969** » Wed Mar 05, 2008 6:24 am

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

ray.wurlod · Post by **ray.wurlod** » Wed Mar 05, 2008 6:25 am

Yes it did.

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

admiral1969 · Post by **admiral1969** » Wed Mar 05, 2008 6:35 am

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

No comments...

ray.wurlod · Post by **ray.wurlod** » Wed Mar 05, 2008 6:47 am

Yes they are, according to DataStage's rules.

admiral1969 · Post by **admiral1969** » Wed Mar 05, 2008 7:11 am

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

ray.wurlod · Post by **ray.wurlod** » Wed Mar 05, 2008 3:09 pm

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.

chulett · Post by **chulett** » Wed Mar 05, 2008 3:18 pm

Don't let Larry hear you say that!