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
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 ?
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.
