topic MySQL -> MsSQL "zero date" problem in Talend Studio

MySQL -> MsSQL "zero date" problem

_AnonymousUser — Sat, 16 Nov 2024 14:34:56 GMT

Hi All,
In TOS 2.1.0.RC1_r4264 (Java), when I try to transfer a "zero date" (0000-00-00 00:00:00) from a tMySQLInput to a tMSSQLOutput, I have the following java errors :
Exception in component tMSSqlOutput_1
disconnected
java.sql.SQLException: Only dates between January 1, 1753 and December 31, 9999 are accepted.
at net.sourceforge.jtds.jdbc.DateTime.packDate(DateTime.java:300)
at net.sourceforge.jtds.jdbc.DateTime.<init>(DateTime.java:121)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.setParameter(JtdsPreparedStatement.java:361)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.setTimestamp(JtdsPreparedStatement.java:757)
at projet_bi.timesheet_nt_tmp.timesheet_NT_tmp.tMysqlInput_1Process(timesheet_NT_tmp.java:360)
at projet_bi.timesheet_nt_tmp.timesheet_NT_tmp.main(timesheet_NT_tmp.java:528)
And indeed, when I enable traces, this is how the "zero date" is interpreted by TOS : Sun Nov 30 00:00:00 CET 2.
Of course, SQL Server can't add it, because it's not between the right interval...
What can I do in order to make those dates transfer correctly (I tried to write a code snippet in a tJavaRow component in order to modifiy this blank date, but I can't load java.Utils.Date !) ?
Thanks,
Cyril Sonnefraud

Re: MySQL -> MsSQL "zero date" problem

Anonymous — Wed, 11 Jul 2007 15:15:12 GMT

Actually, "zero date" cannot be handled by java Date type (min year is year 1). Nevertheless, Date object has a timestamp attribute TOS uses to test if a Date is zero date (and when timestamp = "zero date", the Date displayed is "Sun Nov 30 00:00:00 CET 2").
tMySQLOuput use this trick to save zero date in database (see 1195). tMSSQLOutput should use the same trick to write correct date. Could you please open a bug in the bugtracker ?

Re: MySQL -> MsSQL "zero date" problem

_AnonymousUser — Wed, 11 Jul 2007 15:48:10 GMT

Bug added : http://www.talendforge.org/bugs/view.php?id=1501
Cyril Sonnefraud

Re: MySQL -> MsSQL "zero date" problem

Anonymous — Tue, 17 Jul 2007 10:55:55 GMT

MSSQL cannot handle zero date actually (like most DBMS). You should define a behavior to handle these dates, either set them to NULL in MSSQL, or set them to min date value allowed (January 1, 1753).
Look at the screens below to see how to do to set dates prior to January 1st, 1970 to NULL, with a tMap component.

Re: MySQL -> MsSQL "zero date" problem

Anonymous — Thu, 13 Dec 2018 09:59:34 GMT

Hello. I have same problem with dates from AS400.. I see no screenshoots in your post how to do this in t-map..Thx