Date columns in SQL-Server (MSSQL-JDBC 3.0) running under Java 1.7.0 retrieved as 2 days in the past?

I don't quite have an answer for you. But, I've recreated your situation as you described. It is the same with the jdbc driver v3.101 and v3.202 and v4.

Ctp3 when run under jdk1.7. However, the v2 driver from MS gives your expected answer both under jdk1.6 and jdk1.7. If you need a quick fix and can move to an older jdbc driver, that may work for you.

Up vote 7 down vote favorite share g+ share fb share tw.

I have strange effects when retrieving columns of type DATE from SQLServer2008 using the Microsoft JDBC-Driver version 3.0 when running under the official Oracle JDK 1.7.0. Host OS is Windows Server 2003. All Date columns are retrieved as two days in the past with respect to the value actually stored in the column.

I cooked up a minimal code example the test this out (Test table and data): CREATE TABLE Java7DateTest ( dateColumn DATE ); INSERT INTO Java7DateTest VALUES('2011-10-10'); Code: public class Java7SQLDateTest { public static void main(final String argv) { try { Class. ForName("com.microsoft.sqlserver.jdbc. SQLServerDriver"); Connection connection = DriverManager.

GetConnection( "jdbc:sqlserver://192.168.0.1:1433;databaseName=dbNameHere", "user", "password"); PreparedStatement statement = connection. PrepareStatement("SELECT * FROM Java7DateTest"); ResultSet resultSet = statement.executeQuery(); while (resultSet.next()) { final java.sql. Date date = resultSet.

GetDate("dateColumn"); final String str = resultSet. GetString("dateColumn"); System.out. Println(date + " (raw: " + str + ")"); } resultSet.close(); statement.close(); connection.close(); } catch (final Throwable t) { throw new RuntimeException(t.getMessage(), t); } } } Running this code on above configuration prints: "2011-10-08 (raw: 2011-10-08)".

Under JRE 1.6.0_27 it prints: "2011-10-10 (raw: 2011-10-10)" I could not find anything that seems to relate to my problem with google, so I'm assuming that its either something stupid I overlooked or nobody is using Java7 yet. Can anybody confirm this problem? What are my alternatives if I still want to use Java7?

Edit: The problem occurs even when running with -Xint, so its not caused by Hotspot bugs. Edit2: Old drivers (Microsoft 1.28) work properly with JDK1.7.0 (we were using that driver until maybe two years ago, I think). JTDS also works perfectly fine with the example.

I am considering switching to jTDS, but I am reluctant to do so because I have not the faintest idea what the effects on our productive environment may be. Ideally it should just work, but that what I believed when I switched my dev box to Java7, too. There is one pretty fat database in the production environment, that is too big to create a copy of, for testing (or rather our server has so little disk left).

So setting up a test environment for that one app is not straigthforward, I would have to stitch up a shrinked database for that. Edit3: jTDS has its own set of catches attached. I found a behavioral difference that breaks one of our applications.

ResultSet.getObject() returns different object types for SmallInt columns depending on driver (Short vs Integer). Also jTDS does not implement JDBC4 Connection interface, Connect.isValid() is not supported. Edit4: I noticed last week that MSSQL-JDBC 3.0 refuses to connect to any DB after I updated to JDK1.6.0_29.

JTDS it is then... we switched the productive server yesterday (I fixed tow places where the application was relying on peculiarities of the driver), and so far we had have no problems. Java sql-server date jdbc bugs link|improve this question edited Nov 8 '11 at 14:43 asked Oct 11 '11 at 9:57Durandal1,346111 76% accept rate.

We've sed jTDS in production for several years without a problem. That said, we never tried microsofts drivers. Only think I know thats works a bit different is that jTDS cant (unless its changed now) look up SQL Server instances like someserver\instance - you have to find the port number and do someserver:1234 – Brimstedt Oct 19 '11 at 18:57.

I don't quite have an answer for you. But, I've recreated your situation as you described. It is the same with the jdbc driver v3.101 and v3.202 and v4.

Ctp3 when run under jdk1.7. However, the v2 driver from MS gives your expected answer both under jdk1.6 and jdk1.7. If you need a quick fix and can move to an older jdbc driver, that may work for you. Other thoughts are on how the MS jdbc driver handles dates and conversion of Date objects between SQL Server and the jvm. Since the storage of the date is without a time zone, the interpretation of the Date object by the driver is based on the default time zone for the machine running the jdbc driver.

For instance, if you store a smalldate of '2011-10-11 12:00' and retrieve it from a machine with the default time zone set to GMT-7 then the resulting UTC time of the Date object would be '2011-10-11 19:00'. It could be that there is some change in jdk1.7 that impacts this conversion process in the driver resulting in a wild offset. You might experiment with the ResultSet.

GetDate(column, Calendar) method to see if a Calendar with a specific time zone gets you the result you want or helps make sense of why you are seeing the strange offset in the conversion.

I did experiment with getDate(column, Calendar), passing in a simple Calendar.getInstance(). It did not change the (wrong) result, I suspect the driver does the exact same thing internally when calling getDate(column). The funny thing is, after all, that Timestamp columns are retrieved perfectly fine under JDK1.7.0, regardless of driver version.

– Durandal Oct 19 '11 at 13:52 1 BTW, thanks for confirming that the problem does occur on other installations, so I at least now know that I haven't gone completely crazy. – Durandal Oct 19 '11 at 14:06.

Thank you for your feedback. The Microsoft JDBC Driver for SQL Server does not yet support JRE 1.7. We are aware of the getDate issue between our JDBC driver & JRE 1.7 and we are looking into publishing a hotfix to enable customers to move forward with non-production testing of our driver with JRE 1.7. We will publish a link to the hotfix on our blog once available. blogs.msdn.com/b/jdbcteam/ The hotfix is now available.

blogs.msdn.com/b/jdbcteam/archive/2012/01/20/hotfix-available-for-date-issue-when-using-jre-1-7. Aspx Our blog also contains information on the known issues with JRE 1.6u29 & 1.6u30. Shamitha Reddy Program Manager - Microsoft JDBC Driver for SQL Server.

The hotfix worked for my colleagues. Thanks! – Kylar Feb 23 at 23:15.

I don't have a SQL Server setup, but I can't reproduce your problem with PostgreSQL 9.0 and MySQL 5.1 on Windows 7 x64 with JDK 1.7.0. So JDK 1.7.0 can be excluded from being suspect. I have the impression that the SQL Server JDBC driver is to blame here.

I'd suggest to use the jTDS JDBC driver instead. It has always been praised for its better performance and stability as opposed to the MS-provided SQL Server JDBC driver.

1 I recommend the jtds driver too for SQL Server. – gbn Oct 16 '11 at 14:12 Just FYI: setup same test with jtds v1.2.5 and it works as desired. Only caveat is unclear/no support for failover (sql server db mirror or in a cluster) which has kept my own environments from being able to use it in the past.

– philwb Oct 17 '11 at 16:56 Switching to jTDS turns out to be a problem. MS-Driver used to return a Short when calling resultSet. GetObject(smallIntColumn), while jTDS returns an Integer.

I stumbled over at least one place where that broke something - not very promising. I dunno if MS Driver or jTDS does it properly in this case, or if its not specified at all. Its just another stupid obstacle.

Another problem is jTDS only implements JDBC3 interface, no support for Connection.isValid(). – Durandal Oct 21 '11 at 13:35.

This is also an issue in OpenJDK 1.6.0_20. However, the mssql driver works fine with Suns JRE 1.6.0_16.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.