isValid syntax for non-standard date formats

[abhijitg]

Hi,

I am currently trying to validate a column in my input file that should contain a date.
The date format that I need to validate is as follows:

Code: Select all

Mar  1 2005
Mar 17 2005

However there are non date entries (3, ABCD) coming in as well and I need to write these invalid records to a reject file.
I would like to know the correct syntax when using the isValid function for 'non-standard' date formats.

Here are the things I have tried so far:

Code: Select all

isValid("date",InLink.DateToValidate)
Error:
Data string '3' does not match format '%yyyy-%mm-%dd': the value for tag %yyyy has fewer characters than expected.

isValid("date[%mmm %(d,s) %yyyy]",InLink.DateToValidate)
Error:
The schema type "date" does not take parameters "%mmm %(d,s) %yyyy"

isValid("date %mmm %(d,s) %yyyy",InLink.DateToValidate)
isValid("date(%mmm %(d,s) %yyyy)",InLink.DateToValidate)
Error
Parsing parameters for schema type "date": Expected "[" or EOF; got: "%"

isValid("date",InLink.DateToValidate["%mmm %(d,s) %yyyy"])
Error
Data string '' does not match format '%yyyy-%mm-%dd': an integer was expected to match tag %yyyy.

isValid("date",InLink.DateToValidate,"%mmm %(d,s) %yyyy")
Error
This comes out as an invalid derivation.

Could someone let me know the correct syntax for isValid or a way to override the default date format '%yyyy-%mm-%dd'. We are using DataStage 8.0.1.
Please do not give me examples of the StringToDate function as a 'solution'. There are enough of those out there for people to search .

Thanks
Abhijit

[Mike]

Per the documentation, the variable length day specifier that you are attempting to use is only available for the import operator, so that would rule out using it with the isValid transform function. Replace a space in position 5 with a zero, then use the %dd fixed width specifier.

Mike

[Kryt0n]

From documentation for IsValid: For data types of date, time, and timestamp, you can optionally specify a format string. The format string describes the format that your input data uses when it differs from the default formats for date, time, or timestamp

[Mike]

So, your last example is the proper syntax for the IsValid function except for the limited availability of the %d format specifier.

This should work for you:

Code: Select all

IsValid("date",If InLink.DateToValidate[5,1] =  " " Then InLink.DateToValidate[1,4] : "0" : InLink.DateToValidate[6,6] Else InLink.DateToValidate,"%mmm %dd %yyyy")

Alternatively, you could try using your existing format specifier of "%mmm %(d,s) %yyyy" within your input sequential file (import operator) and let the sequential file stage reject records.

Mike

[Mike]

Another possibility is the buggy 8.0.1 version...

Despite the documentation, I plugged your derivation into version 8.5:

Code: Select all

IsValid("date",InLink.DateToValidate,"%mmm %(d,s) %yyyy")

and it produced the same results as the alternative code in my previous post.

Mike

[abhijitg]

Hi Mike,

I am having an problem using 3 argument in the isValid function. The derivation box is flagged as red whenever I add the third argument.

isValid("date", InLink.DateToValidate) is deemed valid
isValid("date", InLink.DateToValidate,"%yyyy%mm%dd") is flagged red in the deriviation box.

Any suggestions as to why this is the case?

Thanks
Abhijit

[Mike]

Either an 8.0.1 bug or some kind of non-display character in your derivation expression...

Try clearing the derivation, and then building the expression exclusively with the expression builder / helper (i.e. box with the 3 dots).

Mike

[eph]

Hi,

I believe that in 8.0 and 8.1 there is no possibility to give a format string for date validation.

You can do it in two steps : transform string into date, and then validate the date using isvalid. The problem is that if your string does not match the format string in the stringtodate function, the function will abort.
That's why I personally use a parallel routine to validate if the string is correct i.e. if it matches the format string and is a valid date (in calendar).

regards,
Eric

[Kryt0n]

I believe that in 8.0 and 8.1 there is no possibility to give a format string for date validation.

Could explain the issue, can't believe it took them to >8.1 to think of it though.

You can do it in two steps : transform string into date, and then validate the date using isvalid. The problem is that if your string does not match the format string in the stringtodate function, the function will abort.

Makes the second step fairly pointless, if it converts, it clearly is valid

Can try put it through a column import or such ilk that allows reject links, you should then also be able to specify the date format

[eph]

Makes the second step fairly pointless, if it converts, it clearly is valid

Sure, but one might want to have a reject rather than an abort if data doesn't match the format string during conversion. A px routine can recreate the v8.5 behavior of the isValid function.