Hi,
I have a job in which I have a date field which is varchar(10) and I'm getting date record in different formats like mm/dd/yyyy, m/d/yyyy, mm/d/yyyy and m/dd/yyyy example 10/10/2010, 1/1/2010, 11/1/2011 and 1/12/2011.
I want to process record only with mm/dd/yyyy format i.e 10/10/2010.
I used a function IsValid("string", DSLink2.Date[%mm/%dd/%yyyy]),
but this function returns 1 for all the above formats and I taught the function will return 1 for the format data mm/dd/yyyy and all others to 0 .
Please let me know if I'm doing something wrong.
Thanks,
Raju
IsValid Function
Moderators: chulett, rschirm, roy
IsValid Function
somaraju
They are all valid strings, in spite of your (I believe incorrect) date syntax. I would think you'll need to approach this differently: length = 10, count of "/" = 2, one at position 3 and the other at position 6. Or just the length check if you are certain they are always valid dates otherwise.
I also find it odd that you don't consider the other formats some kind of 'valid', why not standardize them rather than (I assume) reject them?
I also find it odd that you don't consider the other formats some kind of 'valid', why not standardize them rather than (I assume) reject them?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Everything is a valid string. Why not specify "date" as the first argument of the IsValid() function, and set a default date format of "%mm/%dd/%yyyy" in the job properties just for this job?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
The derivation IsValid('date', StringToDate(InputField,"%mm/%dd/%yyyy")) yielding "1" for correct date formats and 0 for improper date formats.
Based on the return value "0" we can filter the records by considering those are improper date formats (Raju want to process the records with valid %mm/%dd/%yyyy date mask)
Based on the return value "0" we can filter the records by considering those are improper date formats (Raju want to process the records with valid %mm/%dd/%yyyy date mask)
Cheers
Ravi K
Ravi K
Based on the limited examples Raju gave in his OP, it appears that the dates were generated without leading zeros in the month and day fields. If the business rules dictate to reject all records with dates that do not meet the %mm/%dd/%yyyy physical format regardless of the actual value then the IsValid/StringToDate combination should work. Keep in mind that it will also reject all dates that meet the physical format requirement but fail the valid value test (such as "02/32/2011").
StringToDate with an invalid date will return a string of asterisks when queries as a string ("**********"), which IsValid would of course see as invalid. IsValid has been enhanced with IS 8.5 to include type-specific functions (IsValidDate and so on) and IIRC can also be passed a format string for Date/Time-related datatypes.
Regards,
StringToDate with an invalid date will return a string of asterisks when queries as a string ("**********"), which IsValid would of course see as invalid. IsValid has been enhanced with IS 8.5 to include type-specific functions (IsValidDate and so on) and IIRC can also be passed a format string for Date/Time-related datatypes.
Regards,
- james wiles
All generalizations are false, including this one - Mark Twain.
All generalizations are false, including this one - Mark Twain.