IsValid Function

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
somu_june
Premium Member
Premium Member
Posts: 439
Joined: Wed Sep 14, 2005 9:28 am
Location: 36p,reading road

IsValid Function

Post by somu_june »

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
somaraju
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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?
-craig

"You can never have too many knives" -- Logan Nine Fingers
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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.
Ravi.K
Participant
Posts: 209
Joined: Sat Nov 20, 2010 11:33 pm
Location: Bangalore

Post by Ravi.K »

Try with following derivation.

IsValid('date', StringToDate(InputField,"%mm/%dd/%yyyy"))
Cheers
Ravi K
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

IsValid() expects a string argument. What happens when the data are not a valid date format? The StringToDate() function fails, but what result obtains?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Ravi.K
Participant
Posts: 209
Joined: Sat Nov 20, 2010 11:33 pm
Location: Bangalore

Post by Ravi.K »

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)
Cheers
Ravi K
jwiles
Premium Member
Premium Member
Posts: 1274
Joined: Sun Nov 14, 2004 8:50 pm
Contact:

Post by jwiles »

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,
- james wiles


All generalizations are false, including this one - Mark Twain.
Post Reply