Java JDBC: dates consistently two days off

Question

I am using Java JDBC to write a date to SQL server 2008 and then read it back.
The date that is read back is consistently two days earlier than the date that was actually written.

I am inserting the row containing the Date field with a prepared statement. The date value is provided by:

java.sql.Date todaysDate = new java.sql.Date(System.currentTimeMillis()) ;
System.out.println(todaysDate.toString()) // -> 2012-07-02
ps.setDate(8, todaysDate);

After writing the date to the DB, SQL server shows me the correct date if I run:

select date from table_name where date!=null // ->2012-07-02

If I run the same query via JDBC then retrieve the date value from the resultset using

java.sql.Date sqlDate = rs.getDate("date") ;
sqlDate.toString() // ->2012-06-30

The inserted row is the only row in the table with a non-null date so this does not appear to be a case of reading the wrong record.

I thought this would be a well known problem but the only reference I could find by a Google search for a "two days off" issue had no definitive answer.

Any ideas?

beeky (living in the past)

Answer 1

Faulty JDBC Driver

It turns out the problem was the MS JDBC driver. I tried every possible combination of date types and date conversions and nothing worked. After a great deal of searching (should have done that first!) I saw a comment on an older SO entry that implied the problem was the version 3 JDBC driver from Microsoft. I got the latest driver, version 4.something, and the problem went away.

Thanks to all that tried to help. Special thanks to you Mike for taking the time to post a solution.

-=beeky

Answer 2

For those who are using maven, use this for java 8:

 <dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <version>6.2.1.jre8</version>
 </dependency>

As it is pointed out, if you use an old version, you may face this problem, and it is not easy to debug.

Answer 3

I've run into this problem a couple of times recently and tore my hair out before remembering that if the field is of type date this happens, switching the field type to type datetime solves the problem.

Answer 4

like what user903724 said, change to version 4 that would be fixed, but in my case, When I'm using sqljdbc4-3.0.jar, this issue still reproduces, but I change it to sqljdbc42.jar, this issue got fixed. Hope my experience would be helpful. download from sqljdbc42.jar

Answer 5

If you are using MSSQL 2015, use this Sqljdbc41 to fix this issue

 <!-- https://mvnrepository.com/artifact/com.microsoft.sqlserver/sqljdbc41 -->
<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>sqljdbc41</artifactId>
    <version>6.0.8112</version>
</dependency>

Answer 6

This issues is coming when we are using date DataType in Microsoft sql. I have fixed this to change date to datetime.