Null values when reading DateTime2 Field

[Anonymous]

Hello,

When reading datetime2 fields with tMSSqlInput, all the date values come as null.

I even specify the date pattern "yyyy-MM-dd HH:mm:ss.SSS" in the schema definition, but to no avail.

I have also tried the tMSSqlRow component and it produces the same results.

I would appreciate your help in fixing this issue.

Best regards

[Anonymous · Accepted solution]

You might using Microsoft driver. With jtds you might get Not null

[Anonymous]

Yes i'm using the Microsoft jdbc driver as required per the MSSqlInput component. There is data in the table that I'm querying, but it simply comes as null. 

Another odd, perhaps unrelated, behaviour, is that when I press guess schema based on the query, Talend produces the following error "class not found net.sourceforge.jtds.jdbc.driver". Is there something wrong with the installation?

Edit: Using a jtds jdbc connection to the MsSqlServer fixes the datetime related issue.

[Anonymous]

Changing JDBC provider from Microsoft to open source JTDS as suggested by @rchinta was fix for the titled issue, few days back, when I was running the jobs in development environment.

Now, I m running the same jobs on staging environment which giving an error related to connection : "Invalid state : Connection object is closed". Further, I tried changing JDBC provider to Microsoft, the issue of connection has resolved. But again the null date issue has raised with JDBC provider as Microsoft.

UPDATE : 

In my case Microsoft JDBC retuning Null for Date field, but its perfect with DateTime field.

So, I just changed tMSSqlInput component query for Date field as :

SELECT CAST(Date_Field as Datetime) from Table_Name

instead of :

SELECT Date_Field from Table_Name

I don't know if this is efficient solution or not, but if the issue is driver related then one can go for the above solution.

Hope this would help someone facing the same issue with mssql-jdbc.