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

Two Stars

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

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.

Forteen Stars

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

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

 

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

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Nine Stars

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

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.

Regards David
Dont forget to give Kudos when an answer is helpful or mark the answer as the solution.

Calling Talend Open Studio Users

The first 100 community members completing the Open Studio survey win a $10 gift voucher.

Start the survey

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch