Page 1 of 1
Date validation
Posted: Fri Dec 16, 2011 2:12 am
by 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()
Posted: Fri Dec 16, 2011 2:36 am
by 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.
Posted: Fri Dec 16, 2011 7:07 am
by 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
Posted: Fri Dec 16, 2011 7:36 am
by 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())
Posted: Fri Dec 16, 2011 7:56 am
by chulett
Re-read Mike's post.
Validate then convert only if valid.
Posted: Fri Dec 16, 2011 7:59 am
by 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
Posted: Fri Dec 16, 2011 8:04 am
by pandeesh
Good catch!!
Posted: Fri Dec 16, 2011 8:11 am
by pandeesh
I think this is the time to resolve this topic!!
Posted: Fri Dec 16, 2011 12:43 pm
by vskr72
Thank you everyone for your valuable inputs. I will try it out.
Posted: Fri Dec 16, 2011 12:44 pm
by vskr72
Thank you everyone for your valuable inputs. I will try it out.