Validation of Varchar() field for Date

[Teej]

Hello folks --

Server Job - DS 6.0

I am trying to see if there is a better method to ensure that a passed value onto a Transform Stage is valid. I have an input variable of a Varchar(10) that is a YYYYMMDD date format. I know that I could do an if/then/else bunch of checks by breaking down those fields, validating whether the date is valid, ensuring that the DD is correct for the corresponding MM, particularly for the YYYY format (taking in account the leap year day rule). Not to forget handling those truncated and NULL fields.

Naturally, it will be a pain in the butt to do.

So I'm trying to find a better way. The closest I came was this:

ICONV([field], "D[4,2,2]")
Status()

I believe Status() is a BASIC function, and I must plea ignorance on the inner-working of BASIC at this time. I am trying to handle this via 2 Stage Variables, but I knew it would not work.

My question to you folks -- What is the proper way to obtain the returned Status for an iconv call? How do I include a BASIC function within the Transform stage?

Your help is deeply appreciated.

-T.J.


* * *

... now if this can make breakfast, my life is complete.

[travis]

Did you consider simply using the DateGenericIsDate Transform that's native to DS?

[ray.wurlod]

DateGenericIsDate expects delimiters in the date. But it could be adapted. You can construct a routine something like the following, and use that routine directly in an expression (such as a stage variable derivation expression or an output column derivation).
FUNCTION IsYMDDate(TheString)
$COPYRIGHT "Copyright (c) 2003, Ray Wurlod. All rights reserved."
* The input argument is expected to be a character string containing
* eight to ten characters comprising a date in YYYYMMDD or YYYY-MM-DD
* format (with or without delimiters).
* The function returns 1 (= true) if the string represents a valid
* date, and sets @USER0 to the internal format of that date. The
* function returns 0 (= false) if the string does not represent a
* valid date (including truncated fields) and @USER0 is "".
* The function returns NULL (and @USER0 is NULL) if TheString is NULL.
*
If IsNull(TheString)
Then
Ans = @NULL
@USER0 = @NULL
End
Else
@USER0 = Iconv(TheString, "DYMD")
Ans = Not(Status())
If Ans Then @USER0 = ""
End
RETURN(Ans)
(The @USER0 variable can also be accessed in an expression after this function has been invoked, provided that the expression is executed within the same process.)


Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

[kcbland]

Here's my personal favorite code, which has extra conditions for when the ICONV function fails to recognize some standard formats (YYYY-MM-DD, sheesh, how could those guys in England miss that one!) or some non-standard formats.

The most import thing is to give ICONV a chance. If ICONV returns blank (""), then try to match some variants it can't recognize. The function returns the internalized value of a date. If one isn't calculated, it returns blank (""). I use this as a nested function for TRANSFORMS or other ROUTINES to standardize my output. The SDK stuff in 6.0 STILL doesn't handle YYYY-MM-DD, and even after I helped the guys who wrote the original SDK waaaayyyy back.


Date = Arg1
IntDate = ICONV(Date,"D")
If IntDate = "" Then
CONVERT "/ :.,-_" TO "--------" IN Date
Begin Case
Case Date MATCHES '0n"-"0n"-"0n'
yy = Field(Date,"-",1)
mm = Field(Date,"-",2)
dd = Field(Date,"-",3)
IntDate = ICONV(mm:"-":dd:"-":yy,"D")
Case LEFT(Date,10) MATCHES '4n-2n-2n'
Date = LEFT(Date,10)
yy = Field(Date,"-",1)
mm = Field(Date,"-",2)
dd = Field(Date,"-",3)
IntDate = ICONV(mm:"-":dd:"-":yy,"D")
CASE @TRUE
IntDate = ""
End Case
End
Ans = IntDate


Kenneth Bland

[Teej]

Thanks for your help on this matter.

Everything appears to be working at this time. Of course, that's relative (I just found out that a Right() would truncate the negative sign if the value was negative for PX... *groan*).

-T.J.


* * *

... now if this can make breakfast, my life is complete.