Converting date '0000-00-00' from MySQL to Java

Anonymous — Sat, 16 Nov 2024 13:18:48 GMT

Hello,
I'm using TOS 4 to extract data from a MySQL database to another MySQL DB.
In the datasource, it seems that some dates are filled with '0000-00-00', which leadds to a problem when the job tries to manipulate data, even if the field is not mapped.
here's the generated error:
Exception in component tMysqlInput_1
java.sql.SQLException: Value '0000-00-00' can not be represented as java.sql.Date
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1056)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
at com.mysql.jdbc.ResultSet.getDateFromString(ResultSet.java:2129)
at com.mysql.jdbc.ResultSet.getStringInternal(ResultSet.java:5833)
at com.mysql.jdbc.ResultSet.getString(ResultSet.java:5645)
at bo_project_mgt_kpi.ods_risques_alertes_0_1.ODS_Risques_Alertes.tMysqlInput_1Process(ODS_Risques_Alertes.java:2899)
at bo_project_mgt_kpi.ods_risques_alertes_0_1.ODS_Risques_Alertes.tMysqlConnection_2Process(ODS_Risques_Alertes.java:1393)
at bo_project_mgt_kpi.ods_risques_alertes_0_1.ODS_Risques_Alertes.tMysqlConnection_1Process(ODS_Risques_Alertes.java:1281)
at bo_project_mgt_kpi.ods_risques_alertes_0_1.ODS_Risques_Alertes.tFileInputProperties_1Process(ODS_Risques_Alertes.java:1088)
at bo_project_mgt_kpi.ods_risques_alertes_0_1.ODS_Risques_Alertes.runJobInTOS(ODS_Risques_Alertes.java:7512)
at bo_project_mgt_kpi.ods_risques_alertes_0_1.ODS_Risques_Alertes.main(ODS_Risques_Alertes.java:7329)

I read some threads about this problem (known as bug in the version 2), I tried to use "zeroDateTimeBehavior=convertToNull" additional parameter, but it seems to be not taken into account.
Please could you help me with this problem?
Here are the TOS and Java versions:
TOS:
Version: 4.0.2
Build id: r43696-20100604-0918
java version "1.6.0_21"
Java(TM) SE Runtime Environment (build 1.6.0_21-b07)
Java HotSpot(TM) Client VM (build 17.0-b17, mixed mode, sharing)

Thanks.

Re: Converting date '0000-00-00' from MySQL to Java

Anonymous — Tue, 17 Aug 2010 08:20:50 GMT

Hi
You can find a solution on 12687.
Best regards
Shong

Re: Converting date '0000-00-00' from MySQL to Java

Anonymous — Fri, 20 Aug 2010 08:20:34 GMT

Hello,
thank you for your answer.
Following your advice, I added this line to job properties:
AdditionalParams=noDatetimeStringSync=true&zeroDateTimeBehavior=convertToNull
and it is working fine now.

Re: Converting date '0000-00-00' from MySQL to Java

rameshramaswamy — Sun, 31 May 2020 19:13:21 GMT

Thank you for this solution. I got similar problem in Java 11 version and Talend 7.3. I heard noDatetimeStringSync=true is deprecated in Java 11.

I used this option

AdditionalParams="zeroDateTimeBehavior=convertToNull"

Re: Converting date '0000-00-00' from MySQL to Java

Anonymous — Mon, 01 Jun 2020 03:10:26 GMT

@rchinta, thanks for sharing the information and solutions.

topic Converting date '0000-00-00' from MySQL to Java in Talend Studio

Converting date '0000-00-00' from MySQL to Java

Re: Converting date '0000-00-00' from MySQL to Java

Re: Converting date '0000-00-00' from MySQL to Java

Re: Converting date '0000-00-00' from MySQL to Java

Re: Converting date '0000-00-00' from MySQL to Java