handle 'm/d/yyyy' and 'mm/dd/yyyy'

[getsatish_gk]

I have a date field in string format from the input link.. values could be in format 'm/dd/yyyy' or 'mm/d/yyyy' i.e, ex: '1/28/1992'.or '01/8/1992'

I am using below function to handle it
StringtoDate(Input_Link.Column,"%m/%dd/%yyyy") but this works only for 'm/dd/yyyy' input dates

Please suggest a common way to handle this...
appending '0' in single digit date/month from source is my last option

Thanks!

[ray.wurlod]

Search the Parallel Job Developer's Guide manual for "Date and Time Formats" (in Chapter 2). The part about variable forms follows the main table of string format components.

Code: Select all

"%(m,s)/%(d,s)/%yyyy"

[getsatish_gk]

Thanks ray, I did refered this and using this
StringtoDate(Input_Link.Column,"%(m,s)/%(d,s)/%yyyy")) this should handle single and double digits of DAY and MONTH.. but still I'm having this warning in the log

Invalid date value in getInputFieldAsDate() for field '0' of input dataset '0'.


Please suggest.

[qt_ky]

What is the invalid date's actual value? Are you getting any records with 3 digit days or months, 5 digit years, etc.?

[chulett]

As noted, sounds like you are getting bad date values. You might want to use an IsValid check before you do the conversion and only convert good dates.

Funny, saying "bad dates" always makes me think of Indiana Jones and that poor monkey. Ok, maybe not all that funny.

[qt_ky]

If the incoming value were 12/33/2013, it should flag it as an invalid date. Try adding a reject to route the invalid date values for review.

If you anticipate an upcoming bad date, you may want to propose to meet on an invalid date.

[ray.wurlod]

If your file has column headings and your Sequential File stage has "First line is column headings" set to False, the column heading itself will generate an invalid value.