topic Re: [resolved] SQL 2008 Date-Type not supported? (TIS) in Talend Studio

[resolved] SQL 2008 Date-Type not supported? (TIS)

PYUR-QLIK-TALEND — Thu, 29 Mar 2012 22:41:28 GMT

When I try to import a DB2(AS400)-DATE-Field like 0001-01-01 via tMSSqlOutput (-DATE-Field) to MS SQLServer 2008 an error occurs:
Exception in component tMSSqlOutput_1
java.sql.SQLException: Only dates between January 1, 1753 and December 31, 9999 are accepted.
at net.sourceforge.jtds.jdbc.DateTime.packDate(DateTime.java:318)
at net.sourceforge.jtds.jdbc.DateTime.<init>(DateTime.java:126)
All used datatypes (AS400-DATE, MSSQL-DATE and Talend-Java-"Date") supportes Dates befor 1753. Do the Talend- MS-SQl-Server-JDBC-driver only support MSSQL-Server 2005?!

Has anyone had experience so? (We use Team Edition 4.1.2)

Re: [resolved] SQL 2008 Date-Type not supported? (TIS)

Anonymous — Fri, 30 Mar 2012 02:22:05 GMT

Hi
First, please take a look at the below page to know the date and time type in of SQL Server 2008.
https://www.mssqltips.com/sqlservertip/1616/sql-server-2008-date-and-time-data-types/
datetime variables store 8-byte time and date values ranging from January 1, 1753 to December 31, 9999 with an accuracy of 3.33 milliseconds.
datetime2 variables use between 6-8 bytes to store dates and times between January 1, 0001 and December 31, 9999 with an accuracy of 100 nanoseconds.
So, you need to change the type of column from datetime to datetime2 in database to keep 0001-01-01 in target table.
If you can't change field type for some reason, you can set it to null if the date prior to January 1st, 1970. see a similar topic and get the solutions in
https://community.talend.com/t5/Design-and-Development/MySQL-gt-MsSQL-quot-zero-date-quot-problem/td-p/62097
Best regards
Shong

Re: [resolved] SQL 2008 Date-Type not supported? (TIS)

PYUR-QLIK-TALEND — Fri, 30 Mar 2012 08:13:35 GMT

Thanks, shong, for your reply.
I'm afraid you missunderstood my describe. I know the difference between datetime and datetime2. I dont't wont insert in a datetime(2)-Field. I tried to insert in a DATE-Field:
"date variables use 3 bytes to store a date only (with no time information) in the range January 1, 0001 through December 31, 9999." (from your first link 😉
Nevertheless I get the error...
Please have a look at the uploaded screenshots.

Re: [resolved] SQL 2008 Date-Type not supported? (TIS)

Anonymous — Fri, 30 Mar 2012 08:56:06 GMT

Hi
Yes, the Date type should work with January 1, 0001. Not sure the date is read correct from the source database, try to add a tLogRow after tAs400Input to print the date on the console.
Best regards
Shong

Re: [resolved] SQL 2008 Date-Type not supported? (TIS)

PYUR-QLIK-TALEND — Fri, 30 Mar 2012 09:32:39 GMT

Trust me, shong, I tested much variants before I opened this task 😉
All results in tLogRow was correctly. May be, there is a testing-routine in tMSSQL_Output-component, to protect inserting a wrong MSSQL-200 5-date(time). - But our Server is really 200 8 - i tested a "manually" insert to this field (01.01.0001) - no problem.
If(!) this is indeed a bug - it's shure, TIS-Version 5... -tMSSQL_Output works fine? In about 4 weeks, we'll try to update our TIS 4.1.2. But betweenwhiles we could not continue to work. And if the update fails... 😞
regards -paula11

Re: [resolved] SQL 2008 Date-Type not supported? (TIS)

Anonymous — Sat, 31 Mar 2012 07:24:36 GMT

Hi
According to what you said in the topic, I can reproduced the issue.
I inserted in a DATE-Field and got the same error.
I 'm sure this is not due to bug in jtds.
If I change the JDBC to ODBC, the error still recurs indeed.
Besides, every time I retrieve schema of this DATE filed table.
It will changed into DATETIME automatically.
It seems that Talend will regard all DATE filds as DATETIME.
Please report it on BugTracker.
JDBC: ERROR
ODBC: ERROR
tMSSQLROW: Works fine.
Regards,
Pedro

Re: [resolved] SQL 2008 Date-Type not supported? (TIS)

PYUR-QLIK-TALEND — Mon, 02 Apr 2012 17:52:17 GMT

Hi Pedro,
thank you for your investigation. I reported this problem on BugTracker now.
Do you know, whether this bug still exists in (TIS-) version 5?
Regards
-paula11

Re: [resolved] SQL 2008 Date-Type not supported? (TIS)

Anonymous — Thu, 05 Apr 2012 02:49:28 GMT

Hi Paula11
Yes. I reproduced this issue in TIS 5.0.1.
As you have reported this issue on BugTracker, you can trace the process and get info about fixed version.
Regards,
Pedro

Re: [resolved] SQL 2008 Date-Type not supported? (TIS)

PYUR-QLIK-TALEND — Thu, 05 Apr 2012 10:03:50 GMT

Hi Pedro,
many thanks for your test!!!
Our TIS-update-link refer to Version 5.0. 2. But I suspect there is the same bug too. So we really have to wait for a fixed version, before we can make the update. (I'll trace it in Bugtracker.) Hope, it will not last too long...
Regards
-Paula11