Unparseable date

[JMArtin1594762559]

I have seen several similar threads but not quite the same as my issue.

My job was working successfully until the source file string date format changed from yyyy-MM-dd to MM/dd/yyyy.

Previously, this tMap Var Expression was working:

TalendDate.parseDate("yyyy-MM-dd",row1.EffectiveDate )

Now, if I change it to

TalendDate.parseDate("MM/dd/yyyy",row1.EffectiveDate )

I receive error:

Exception in component tMap_1 (TEST_FILE)

java.lang.NullPointerException

at java.text.SimpleDateFormat.parse(SimpleDateFormat.java:1439)

at java.text.DateFormat.parse(DateFormat.java:364)

at routines.TalendDate.parseDate(TalendDate.java:929)

at routines.TalendDate.parseDate(TalendDate.java:887)

at tn_wi.t555_tn_fmap_attest_file_0_1.TEST_FILE.tFileList_1Process(TEST_FILE.java:1535)

at tn_wi.t555_tn_fmap_attest_file_0_1.TEST_FILE.runJobInTOS(TEST_FILE.java:3480)

at tn_wi.t555_tn_fmap_attest_file_0_1.TEST_FILE.main(TEST_FILE.java:3281)

[statistics] disconnected

But if I change it to

TalendDate.parseDate("MM -dd-yyyy",row1.EffectiveDate )

I receive error:

Exception in component tMap_1 (TEST_FILE)

java.lang.RuntimeException: java.text.ParseException: Unparseable date: "9/14/23"

at routines.TalendDate.parseDate(TalendDate.java:943)

at routines.TalendDate.parseDate(TalendDate.java:887)

at tn_wi.t555_tn_fmap_attest_file_0_1.TEST_FILE.tFileList_1Process(TEST_FILE.java:1766)

at tn_wi.t555_tn_fmap_attest_file_0_1.TEST_FILE.runJobInTOS(TEST_FILE.java:3356)

at tn_wi.t555_tn_fmap_attest_file_0_1.TEST_FILE.main(TEST_FILE.java:3106)

Caused by: java.text.ParseException: Unparseable date: "9/14/23"

at java.text.DateFormat.parse(DateFormat.java:366)

at routines.TalendDate.parseDate(TalendDate.java:929)

It seems Talend does not recognize the forward slash(/) but it does recognize the hypen (-).

[Anonymous]

Hello

"9/14/23" has the date pattern "MM/dd/yy", so change the expression to be:

TalendDate.parseDate("MM/dd/yy",row1.EffectiveDate )

About NPE, go to check if there exists null value in the column EffectiveDate.

if you want the output to be null when the input data is null, change the expression to be:

row1.EffectiveDate==null?null:TalendDate.parseDate("MM/dd/yy",row1.EffectiveDate )

Regards

Shong

[RutMitchell]

HY there i can see your post and i must say

1. Locale Considerations: Be aware that date formats can vary by locale. If your date string includes month or day names, ensure that the locale settings are appropriate.
2. Date Parsing Library: Use a dedicated Pay Military Star Card date parsing library or function provided by your programming language or framework. These libraries are designed to handle various date formats and edge cases.
3. Input Data Validation: Validate the input data before parsing to ensure it conforms to the expected format. This can help prevent errors caused by invalid or unexpected date strings.

Thanks and regards

TroyGuillen

[JMArtin1594762559]

@Shicong Hong​ The string format in the Excel file is not "9/14/23". It was actually "9/14/2023".

I had manually tried changing the format inside of Excel from General to Date. That is what caused the year to truncate. Without altering the Excel file, the error message is:

xception in component tMap_1 (T555_TN_FMAP_ATTEST_FILE)

java.lang.RuntimeException: java.text.ParseException: Unparseable date: "7/1/2022"

at routines.TalendDate.parseDate(TalendDate.java:943)

at routines.TalendDate.parseDate(TalendDate.java:887)

at tn_wi.t555_tn_fmap_attest_file_0_1.T555_TN_FMAP_ATTEST_FILE.tFileList_1Process(T555_TN_FMAP_ATTEST_FILE.java:1516)

at tn_wi.t555_tn_fmap_attest_file_0_1.T555_TN_FMAP_ATTEST_FILE.runJobInTOS(T555_TN_FMAP_ATTEST_FILE.java:3213)

at tn_wi.t555_tn_fmap_attest_file_0_1.T555_TN_FMAP_ATTEST_FILE.main(T555_TN_FMAP_ATTEST_FILE.java:3014)

Caused by: java.text.ParseException: Unparseable date: "7/1/2022"

at java.text.DateFormat.parse(DateFormat.java:366)

at routines.TalendDate.parseDate(TalendDate.java:929)

[JMArtin1594762559]

@Troy Guillen​ Are you referring to my Windows date and region settings? The short date does appear to be the same as the input data.  

The expression was working before when the input date included hyphens instead of forward slashes. It appears to match the syntax given inside of Talend. Is this what you mean by date parsing library?

 

There error message appears to be failing on the "7/1/2022" value in the first row of the Excel file.

 

[David_Beaty]

Hi, to resolve problems where dates can change or a given column date format can be different per row, I’ve previously created a Java helper routine that you can pass in the textual value and a delimited string of date formats to try, in order.

something like:

ConvertDate(row1.EffectiveDate,”dd/mm/yyyy;yyyy/mm/dd”)

[Anonymous]

@Jason MArtin print the data on the console to see what's the data format is read from Excel file before tMap, and use the right format to parse the string date.

[ThWabi]

Hello JMArtin,

if your date data does not contain leading zeros for month (and/or day), you might try "M/d/yyyy".

Best regards,

Thomas