CODEWITHSUNDEEP
javasql-serverdatejdbc
user903724
user903724

Reputation: 3026

Java JDBC: dates consistently two days off

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)

Upvotes: 27

Views: 10712

Answers (9)

user903724
user903724

Reputation: 3026

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

Upvotes: 22

Virudhai Paul
Virudhai Paul

Reputation: 101

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>

Upvotes: 2

Nirmal Kumar
Nirmal Kumar

Reputation: 11

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

Upvotes: 1

Hasson
Hasson

Reputation: 1914

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.

Upvotes: 5

Edson Sousa
Edson Sousa

Reputation: 1

I had the same exact problem even using sqljdbc4 with Java 8.

Once i don´t need this field to make any type of comparison in the application i solved the issue by casting the field in my query like this: CAST(dbo.tblPwActividadeParticipanteDetalhe.Data AS VARCHAR(10)).

Upvotes: 0

Kun
Kun

Reputation: 580

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

Upvotes: 2

Bill Harrelson
Bill Harrelson

Reputation: 31

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.

Upvotes: 2

GerritCap
GerritCap

Reputation: 1626

I had exactly the same problem. The 2 day offset went away as soon as I used java 6 runtime environment instead of a java 7 runtime environment.

So it is possibly also a difference between the JDBC version 4.1 backwards compatibility with JDBC 3 drivers.

Upvotes: 0

GingerHead
GingerHead

Reputation: 8230

Your problem is Time Zone values ("GMT").
You need to introduce this manipulation in your JDBC fetching method as follows: 

Calendar gmt = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
PreparedStatement stmt = connection.prepareStatement(sql);
stmt.setDate(1, new Date(0)); // I assume this is always GMT
ResultSet rs = stmt.executeQuery();
rs.next();

//This will output 0 as expected
System.out.println(rs.getDate(1, gmt).getTime());

Upvotes: 0

Related Questions