I have a date column that has a value like 11/30/20011. THis is a an invalid date format. SO, I was trying to use this below condition in transformer and Iw as expecting the value to be NULL.But it turns out that it is giving a value like 11/30/2001. Looks like it is takign the first 4 characters of the year. I think I am missing something here. Can you pls help?
The second argument to the IsValid function should be your unconverted input string. Since you have converted it to a date and specified a format string, you have explicitly converted the string to a date using the first four digits of the year, which then, of course, passes the IsValid function.
if len(Field(ToTfm.AS_OF_DATE,'/',3))<>4 then SetNull() else (If IsValid('date', StringToDate(ToTfm.AS_OF_DATE,"%mm/%dd/%yyyy")) then StringTODate(ToTfm.AS_OF_DATE,"%(m,s)/%(d,s)/%yyyy") else SetNull())