Bug in MS SQL Server 2005 JDBC 1.1 Drive when ResultSet.getDate() used on datetime/smalldatetime?

Posted on 2007-08-03
Last Modified: 2010-05-18
Hello, I have encountered what I suspect is a serious, data corrupting bug in MS SQL Server JDBC Driver 1.1 (downloaded from Microsoft 2-August-2007)? However, I find it hard to believe that I have found a bug, using a commonly used method, ResultSet.getDate on a datetime or smalldatetime column. The bug is that getDate changes the date by making the month = 0 and day = 1 (in Java terms ... in human terms it makes the month 1 and day 1). Below is my code. My question / request is to run this code using the MS SQL Server 2005 JDBC Driver 1.1 sqljdbc.jar
and paste in the result and whether or not you get the error. Thank you.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

//import java.sql.*;

 * @author pd 7/31/07
 * Demonstrate bug in MS SQL Server Driver 1.1 where ResultSet getDate
 * returns January 1 no matter what the date actual is.
public class TestSqlServerDates {

      public static void main(String[] args) {

      Connection connection=null;
      Statement statement=null;
      ResultSet rs=null;
            // Load the JDBC driver
            try {
         connection = DriverManager.getConnection("jdbc:sqlserver://localhost;database=yourdatabase", "user", "password");
            } catch (ClassNotFoundException e) {
                  System.out.println("Error loading JDBC driver: " + e.getMessage());
            } catch (SQLException e) {
         System.out.println("Error getting connection: " + e.getMessage());

      // Create and load temporary table
      try {
         statement = connection.createStatement();
         statement.execute("CREATE TABLE #TestDates (seq INT PRIMARY KEY, date1 DATETIME, date2 SMALLDATETIME)");
         statement.executeUpdate("INSERT INTO #TestDates VALUES (1, '2007-09-30', '2007-10-31')");
      } catch (Exception e) {
         System.err.println("Exception: " + e.getMessage());
      } finally {
      // execute query
      try {
         statement = connection.createStatement();
         rs = statement.executeQuery("SELECT seq, date1, date2 FROM #TestDates");
         if ( {
            int seq = rs.getInt("seq");
            String date1String = rs.getString("date1");
            Date date1GetDate = rs.getDate("date1");
            Date date1GetTimestamp = rs.getTimestamp("date1");
            Date date1GetObject = (Date) rs.getObject("date1");
            System.out.println("date1 datetime     " + "  getString: " + date1String + "  getDate: " + date1GetDate + "  getTimestamp: " + date1GetTimestamp + "  getObject: " + date1GetObject);
            Date checkDate1 = new Date(2007-1900, 9-1, 30);
            if (date1GetDate.compareTo(checkDate1)!=0) System.out.println("**** Method getDate returned invalid result *****");
            String date2String = rs.getString("date2");
            Date date2GetDate = rs.getDate("date2");
            Date date2GetTimestamp = rs.getTimestamp("date2");
            Date date2GetObject = (Date) rs.getObject("date2");
            System.out.println("date2 smalldatetime" + "  getString: " + date2String + "  getDate: " + date2GetDate + "  getTimestamp: " + date2GetTimestamp + "  getObject: " + date2GetObject);
            Date checkDate2 = new Date(2007-1900, 10-1, 31);
            if (date2GetDate.compareTo(checkDate2)!=0) System.out.println("**** Method getDate returned invalid result *****");
         } else {
            throw new RuntimeException("No rows returned");
            } catch (Exception e) {
                  System.err.println("Exception: " + e.getMessage());
            } finally {
         // drop temporary table
         try {
            statement = connection.createStatement();
            statement.execute("DROP TABLE #TestDates");
         } catch (Exception e) {}

Question by:pdegregorio

    Author Comment

    PS here's what I get
    date1 datetime       getString: 2007-09-30 00:00:00.0  getDate: 2007-01-01  getTimestamp: 2007-09-30 00:00:00.0  getObject: 2007-09-30 00:00:00.0
    **** Method getDate returned invalid result *****
    date2 smalldatetime  getString: 2007-10-31 00:00:00.0  getDate: 2007-01-01  getTimestamp: 2007-10-31 00:00:00.0  getObject: 2007-10-31 00:00:00.0
    **** Method getDate returned invalid result *****

    Author Comment

    I found this on Microsoft forum ...
    which explains that MS does not officially support the IBM JVM with version 1.1 but was expecting to issue a hotfix and also expects to support it with 1.2 version currently in test.

    >Answer  Re: SQLServer 2005 JDBC Driver - Date rendered correctly on local environment(windows), >but not on Websphere server (unix)
    >Hi all,
    >Thank you for your feedback and patience on this issue.  We will be issuing a hotfix to the Microsoft >SQL Server 2005 JDBC Driver v1.1 to address this issue by working around certain behavior in the IBM >JVM that ships with Websphere.  Current planned availability of that hotfix is end of April.  Also, the >v1.2 CTP 1 planned for April will also include the fix.  Please note that Microsoft does not officially >support the IBM JVM for use with the v1.1 driver.  Current plan is to support the IBM JVM with the v1.2 >driver.  Until a fix is avaliable, possible workarounds are: Request the value as a String or use another >vendor's JVM.  For example, the Sun JVM does not exhibit this problem.
    >Hope this helps.
    >Thanks again for your continued interest in the Microsoft SQL Server 2005 JDBC driver.
    >--David Olix [MSFT]
    LVL 1

    Accepted Solution

    Closed, 500 points refunded.
    Community Support Moderator

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Suggested Solutions

    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now