Geek Answers Handbook

How should SQL Server timestamp2 work in JDBC?

I am having trouble trying to use timestamp2 instead of Timestamp in SQL Server 2008. Obviously rs.getTimestamp has very different behavior between timestamp and timestamp2. However, I cannot find the documentation that says there should be a difference, or that I should use something else. Interesting, I'm just doing something wrong.

Environment:

  • Tried both SQL Express 2008 (10.0) and SQL Server 2008 R2 (10.5).
  • sqljdbc4.jar version 3.0, size 537,303 bytes, CRC-32 = a0aa1e25, MD5 = 402130141d5f2cee727f4371e2e8fd8a.
  • Java 1.6

Here is a unit test example demonstrating the problem. The only "magic" is "Db.getConnection ()", which you can replace with the appropriate code. The test is the same as for datetime, and for datetime2, but the datetime2 test failed with a date earlier than 2 days. I process all times in the database as GMT / UTC, and I did not try to add time zone information to the database data for datetime2 data.

private void testTimestamp(TimeZone gmtTz, Connection conn, String query, Calendar expectedCal) throws SQLException { PreparedStatement stmt = conn.prepareStatement(query); ResultSet rs = stmt.executeQuery(); while (rs.next()) { // Note the expectedCal has a GMT timezone. Date actualTs = rs.getTimestamp("dt", expectedCal); // Just print out the time difference long diff = actualTs.getTime() - expectedCal.getTimeInMillis(); System.out.println("Diff=" + diff); // Do the test to make sure they are the same // In practice, this succeeds for datetime and fails for datetime2 Assert.assertEquals(expectedCal.getTimeInMillis(), actualTs.getTime()); } } @Test public void testDateTime() throws SQLException { Connection conn = Db.getConnection(); TimeZone gmtTz = TimeZone.getTimeZone("GMT"); String query; Calendar expectedCal = Calendar.getInstance(gmtTz); expectedCal.clear(); expectedCal.set(2011, 10, 02, 11, 17); query = "select CAST('2011-11-02 11:17:00' as datetime) as dt"; testTimestamp(gmtTz, conn, query, expectedCal); query = "select CAST('2011-11-02 11:17:00.0000000' as datetime2) as dt"; testTimestamp(gmtTz, conn, query, expectedCal); // results in an error }

Is my only option switching to timestamp?

EDIT: for future Googlers, using sqljdbc4.jar version 3.0, the test does not work on Linux, but passes through Windows. I have not tried the version of sqljdbc4.jar 4.0 that ships with SQL Server 2012.

+1
source share
2 answers

I remember not hearing about the good things regarding the official SQL Server driver and JTDS preference (although I cannot find this link). I would personally go with JTDS (with rigorous testing, of course) or return to a version that does not cause a problem. I did not work with SQL Server, but from the look and feel, datetime2 seems to be the preferred data type, so I probably won't go back. Option (d) is not really a good IMO option. :)

+3
source

If you are using Sun JRE 1.7 with Microsoft JDBC 3.0 driver, see this blog post http://blogs.msdn.com/b/jdbcteam/archive/2012/01/20/hotfix-available-for-date-issue- when-using-jre-1-7.aspx .

If you feel that you have found a bug in our driver, you can report it via Microsoft Connect. https://connect.microsoft.com/SQLServer

+4
source

More articles:


All Articles