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

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 http://www.microsoft.com/downloads/details.aspx?familyid=6d483869-816a-44cb-9787-a866235efc7c&displaylang=en
and paste in the result and whether or not you get the error. Thank you.

// START OF PROGRAM
import java.io.File;
import java.io.IOException;
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 {
                  Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
         connection = DriverManager.getConnection("jdbc:sqlserver://localhost;database=yourdatabase", "user", "password");
            } catch (ClassNotFoundException e) {
                  System.out.println("Error loading JDBC driver: " + e.getMessage());
                  e.printStackTrace();
                  System.exit(0);
            } catch (SQLException e) {
         System.out.println("Error getting connection: " + e.getMessage());
         e.printStackTrace();
         System.exit(0);
      }

      // 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());
         e.printStackTrace();
      } finally {
      }
     
      // execute query
      try {
         statement = connection.createStatement();
         rs = statement.executeQuery("SELECT seq, date1, date2 FROM #TestDates");
         if (rs.next()) {
            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());
                  e.printStackTrace();
            } finally {
         // drop temporary table
         try {
            statement = connection.createStatement();
            statement.execute("DROP TABLE #TestDates");
         } catch (Exception e) {}
            }

      }
}
// END OF PROGRAM
pdegregorioAsked:
Who is Participating?
 
Vee_ModCommented:
Closed, 500 points refunded.
Vee_Mod
Community Support Moderator
0
 
pdegregorioAuthor Commented:
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 *****
0
 
pdegregorioAuthor Commented:
I found this on Microsoft forum ... http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=993838&SiteID=17
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]
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.