DSXchange

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

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?

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?

Try with following derivation.

IsValid('date', StringToDate(InputField,"%mm/%dd/%yyyy"))

IsValid() expects a string argument. What happens when the data are not a valid date format? The StringToDate() function fails, but what result obtains?

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