topic Re: isDate in tMap Expressioneditor still throws a java.sql.SQLException in Talend Studio

isDate in tMap Expressioneditor still throws a java.sql.SQLException

Anonymous — Sat, 16 Nov 2024 07:52:01 GMT

Hi,

i´m using a tFileInputDelimited to read a csv file, which is connected to a tMap. The tMap then is connected to a tSchemaComplianceCheck, which then is connected to a tMSSqlOutput.

Inside the tMap i use the Expressioneditor for date fields, as we are struggling with invalid date data.

TalendDate.isDate(StringHandling.TRIM(row1.RBKP_BLDAT), "dd.MM.yyyy") ? TalendDate.parseDate("dd.MM.yyyy", StringHandling.TRIM(row1.RBKP_BLDAT)) : null

So the input stream should be checked by the TalendDate.isDate function. If the date is a valid date then it should be passed to the TalendDate.parseDate function for the output stream, if it´s not a valid date it should simply pass NULL to the output stream.

The invalid date is:

17.02.0118

So what i am asking is: why does the TalendDate.isDate function return true instead of false for that date, and therefor an invaled date for the output stream?

I´m getting a

Exception in component tMSSqlOutput_1
java.sql.SQLException: Only dates between January 1, 1753 and December 31, 9999 are accepted.

Or how could i make sure always pass valid dates to the tMSSqlOutput stream?

Thanks in advance.

Re: isDate in tMap Expressioneditor still throws a java.sql.SQLException

manodwhb — Thu, 02 Aug 2018 10:33:03 GMT

@bey0nd,in a tMap of under Activate /unactivate expression filter specify the below way and let me know.

TalendDate.isDate(date,"dd.MM.yyyy")

Re: isDate in tMap Expressioneditor still throws a java.sql.SQLException

David_Beaty — Thu, 02 Aug 2018 13:30:10 GMT

If I'm understanding your explanation correctly, the problem is that 17.02.0118 is a valid date (17th February 118), so Talend is happy, however, SQL Server doesn't like a date in the year 118, hence its message about the year must be between 1753 and 9999.

You probably need some kind of data quality check to ensure that dates are within a sensible range.

Thanks

David.