Select SQL Date type issue

, ,
1

Hi All,

I am using JDBC adapter to conenct microsoft sql server.

I want to fetch the data from one of the table.

I am using sql select template for this. I am also able to get the data. However values for date field is not comminng properly. let’s say if in DB table it is 2018-01-19 09:36:03.183 then I am receiving it as 2018-01-17 09:36:03.183.

The column type for date field is showing as datetime2 and JDBC type is TIMESTAMP in designer. I have used all the field type value like java.lang.string,java.sql.timestamp, java.util.data, java.lang.object still no proper value is comming.

Can anyone please let me know what could be the reason for this. I am using 9.12 IS.

2

Hi,

which version of JDBC Adapter are you using?
Which driver?

Any fixes applied?

Regards,
Holger

3

Hello NB,

Looks like a bug to me in WmJDBC Adapter code itself, I tried with latest fix levels and jars as below: You may have to contact SAG support to get more details about this datatype.

Adapter Version 9.10
Updates JDBC_9.10_Fix8

sqljdbc4.jar

It works fine with datatype: datetime

Kindly let me know if you could manage to fix it.

4

You may try the below literals mentioned in the links:

It might give you some insight, HTH

5

Hi ,

dependent on JVM version, database server version and JDBC Adapter version being used,
you should use the proper jar file for the driver being available for download from microsoft.

Version for the Driver should be at least Sqljdbc 6.0.7728.100 (dated from 30.09.2016), but there might be a newer version (6.2.2.20 dated 17.10.2017 or newer) available.
This package contains 4 jar files (2 for 6.2.2.0 and newer), where the names refer to the JDBC Standard being supported:
sqljdbc.jar JDBC 3.x (removed with 6.0.8112.100)
sqljdbc4.jar JDBC 4.0 (removed with 6.0.8112.100)
sqljdbc41.jar JDBC 4.1 (will require Java 7 and newer; jar file name might have changed with 6.2.2.0)
sqljdbc42.jar JDBC 4.2 (will require Java 8 and newer; jar file name might have changed with 6.2.2.0)

Please check the Readmes for JDBC Adapter (+Fixes) as well as for the MSSQL Server version which JDBC Standard is supported.

Regards,
Holger

6

I definitely think it’s worth getting to the bottom of the issue. If all else fails, however, one thing to consider is to use a CONVERT function in your SELECT statement so you get the date back as a string. In fact, in webMethods it’s a fairly common practice to deal with strings only.

Percio