Page 1 of 1
isValid syntax for non-standard date formats
Posted: Thu Feb 16, 2012 7:43 pm
by 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:
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
Posted: Thu Feb 16, 2012 8:14 pm
by 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
Posted: Thu Feb 16, 2012 9:02 pm
by 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
Posted: Fri Feb 17, 2012 7:43 am
by 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
Posted: Fri Feb 17, 2012 9:40 am
by 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
Posted: Fri Feb 17, 2012 9:57 am
by 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
Posted: Fri Feb 17, 2012 10:08 am
by 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
Posted: Tue Feb 21, 2012 4:52 am
by 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
Posted: Tue Feb 21, 2012 3:29 pm
by Kryt0n
eph wrote: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.
eph wrote: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
Posted: Wed Feb 22, 2012 3:29 am
by eph
Kryt0n wrote: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.