IS_VALID_DATE

[pan_cheng]

Hi Gurus,

I want to capture Invalid dates.I am using IS_VALID_DATE function in validation transform to check whether the date is valid or not. For example if the Input filed FILE_DATE has invalid date value like 20111550 i would like to store that invalid value. How ever after using IS_VALID_DATE function in Validation transform the invalid date value is converted into blank. I want to capture the invalid values so that we know what is invalid. Any idea how to capture it.

Thanks in Advance,

Pan

[venkataramana_paidi · Accepted solution]

Hi Pan,

Invalid date values should be capture  in the invalid table. It never change any data.  I have used excel sheet with some sample data. It is working perfectly. If you are using excel sheet , you should use the format as text for the date field.

For your reference few screenshots.

Thanks & Regards,

Ramana.

[pan_cheng · Accepted solution]

Thanks for your quick response Ramana.

My input field data is in varchar and value is like 20115005 and i am doing validation like below

(is_valid_date(Case_Valid.FileDate, 'YYYY.MM.DD') = 1)

Validation is working fine. But after the validation validation failed table contains Invalid date value as Null instead of 20115005.

Appreciate your help...

Thanks,

[venkataramana_paidi · Accepted solution]

Hi Pan,

Your problem really looking strange. I have checked your data with same syntax that provided by you .

I just changed YYYYMMDD instead of YYYY.MM.DD as we have source data in that format. Anyhow this is not the issue for invalid date field becoming null value.

Please check below post, I hope it will help you. Manoj replied in that thread  if source system input field is not varchar field .

Validation transform - validating date field in source data

Thanks & Regards,

Ramana.

[pan_cheng · Accepted solution]

Thanks Ramana. It worked. The stupid mistake i did is do date conversion in between. Due to that data services is converting the value from invalid date to blank. I was able to capture the invalid date. Thanks for your help.

Thanks,