Date validation

[vskr72]

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?

Code: Select all

If IsValid('date', StringToDate(ToTfm.AS_OF_DATE,"%mm/%dd/%yyyy")) then StringTODate(ToTfm.AS_OF_DATE,"%(m,s)/%(d,s)/%yyyy") else SetNull() 

[Kirtikumar]

I think you would have to use Len function in addition to normal validate function. We had same issue and we ended up adding len function as well.

[Mike]

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.

Mike

[pandeesh]

The below will cover your requirement

Code: Select all

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())

[chulett]

Re-read Mike's post.

Validate then convert only if valid.

[Mike]

Note the difference between these:

Code: Select all

IsValid("date", "11/30/20011", "%mm/%dd/%yyyy")

and

Code: Select all

IsValid("date",StringToDate("11/30/20011", "%mm/%dd/%yyyy"))

Mike

[pandeesh]

Good catch!!

[pandeesh]

I think this is the time to resolve this topic!!

[vskr72]

Thank you everyone for your valuable inputs. I will try it out.

[vskr72]

Thank you everyone for your valuable inputs. I will try it out.