Validating Date

G SHIVARANJANI · Post by **G SHIVARANJANI** » Sun Jul 15, 2007 5:55 am

Hi,

Could any one help me out in validating a date column of a CSV file .

the date format in the column is mm/dd/yyyy......

and i need to validate the date if it is a valid date or not.

thanks,

ray.wurlod · Post by **ray.wurlod** » Sun Jul 15, 2007 6:16 am

Use the IsValid() function. Find it in the Parallel Job Developer's Guide

JoshGeorge · Post by **JoshGeorge** » Sun Jul 15, 2007 6:16 am

Use IsValid() function.
If your input date is not in date format you might want to convert it and use - IsValid('date',StringToDate(InputLink.Date,"%yyyy%mm%dd"))

G SHIVARANJANI · Post by **G SHIVARANJANI** » Sun Jul 15, 2007 6:23 am

I am reading the date column as varchar but not as date, then will this syntax work IsValid('date',StringToDate(InputLink.Date,"%yyyy%mm%dd"))

JoshGeorge wrote:Use IsValid() function.
If your input date is not in date format you might want to convert it and use - IsValid('date',StringToDate(InputLink.Date,"%yyyy%mm%dd"))

chulett · Post by **chulett** » Sun Jul 15, 2007 6:43 am

Try it and see. Correct the date mask so it matches what you've said is in your file first, however.

G SHIVARANJANI · Post by **G SHIVARANJANI** » Sun Jul 15, 2007 6:44 am

Hi,

Isvalid is giving 0 for all the dates

this 12/34/2006 as well as this 01/01/2006

chulett · Post by **chulett** » Sun Jul 15, 2007 6:45 am

Because, as noted, the mask in the supplied example does not match your input format.

G SHIVARANJANI · Post by **G SHIVARANJANI** » Sun Jul 15, 2007 6:47 am

chulett wrote:Because, as noted, the mask in the supplied example does not match your input format. ...

i have given the mask as %mm/%dd/%yyyy

chulett · Post by **chulett** » Sun Jul 15, 2007 6:48 am

Get rid of the slashes.

G SHIVARANJANI · Post by **G SHIVARANJANI** » Sun Jul 15, 2007 6:53 am

yes must be because 1/1/2006 dint match with mm/dd/yyyy

right?

chulett wrote:Get rid of the slashes. ...

chulett · Post by **chulett** » Sun Jul 15, 2007 6:59 am

No.

Does 'yes' mean it is working for you now? If so, please mark the thread as Resolved. If not, please explain what issue remains.

G SHIVARANJANI · Post by **G SHIVARANJANI** » Sun Jul 15, 2007 7:41 am

Hi,

Since i am gettin the dates as d/m/yyyy its not matching with the mask dd/mm/yyyy....

so i have picked up the d and padded it with 0 so that it matches dd and the same way for month as well.

Thanks for the help its working.

chulett wrote:No.

Does 'yes' mean it is working for you now? If so, please mark the thread as Resolved. If not, please explain what issue remains. ...

DSguru2B · Post by **DSguru2B** » Sun Jul 15, 2007 5:20 pm

Yea, but make sure you include a check before padding it with zero. What if the month is 11 or 12 and the day is two digits as well?

ray.wurlod · Post by **ray.wurlod** » Sun Jul 15, 2007 10:29 pm

Why not use three IsValid() functions (with different date format strings) connected by OR ?

G SHIVARANJANI · Post by **G SHIVARANJANI** » Mon Jul 16, 2007 12:54 am

I have put the settings of:

In my PC:

control pannel<enter>

regional and language options<enter>

customiz<enter>

Date<enter>

Short date format<enter>

as MM/DD/YYYY.

this is making the Datastage sequential file to read in DD/MM/YYYY instead of D/M/YYYY.