Date, time, and timestamp values that can cause problems in JDBC and SQLJ applications

You might receive unexpected results in JDBC and SQLJ applications if you use date, time, and timestamp values that do not correspond to real dates and times.

The following items might cause problems:

  • Use of the hour '24' to represent midnight
  • Use of a date between October 5, 1582, and October 14, 1582, inclusive

Problems with using the hour '24' as midnight

The IBM® Data Server Driver for JDBC and SQLJ uses Java™ data types for its internal processing of input and output parameters and ResultSet content in JDBC and SQLJ applications. The Java data type that is used by the driver is based on the best match for the corresponding SQL type when the target SQL type is known to the driver.

For values that are assigned to or retrieved from DATE, TIME, or TIMESTAMP SQL types, the IBM Data Server Driver for JDBC and SQLJ uses java.sql.Date for DATE SQL types, java.sql.Time for TIME SQL types, and java.sql.Timestamp for TIMESTAMP SQL types.

When you assign a string value to a DATE, TIME, or TIMESTAMP target, the IBM Data Server Driver for JDBC and SQLJ uses Java facilities to convert the string value to a java.sql.Date, java.sql.Time, or java.sql.Timestamp value. If a string representation of a date, time, or timestamp value does not correspond to a real date or time, Java adjusts the value to a real date or time value. In particular, Java adjusts an hour value of '24' to '00' of the next day. This adjustment can result in an exception for a timestamp value of '9999-12-31 24:00:00.0', because the adjusted year value becomes '10000'.

Important: To avoid unexpected results when you assign or retrieve date, time, or timestamp values in JDBC or SQLJ applications, ensure that the values are real date, time, or timestamp values. In addition, do not use '24' as the hour component of a time or timestamp value.

If a value that does not correspond to a real date or time, such as a value with an hour component of '24', is stored in a TIME or TIMESTAMP column, you can avoid adjustment during retrieval by executing the SQL CHAR function against that column in the SELECT statement that defines a ResultSet. Executing the CHAR function converts the date or time value to a character string value on the database side. However, if you use the getTime or getTimestamp method to retrieve that value from the ResultSet, the IBM Data Server Driver for JDBC and SQLJ converts the value to a java.sql.Time or java.sql.Timestamp type, and Java adjusts the value. To avoid date adjustment, execute the CHAR function against the column value, and retrieve the value from the ResultSet with the getString method.

The following examples show the results of updating DATE, TIME, or TIMESTAMP columns in JDBC or SQLJ applications, when the application data does not represent real dates or times.

Table 1. Examples of updating DATE, TIME, or TIMESTAMP SQL values with Java date, time, or timestamp values that do not represent real dates or times
String input value Target type in database Value sent to table column, or exception
2008-13-35 DATE 2009-02-04
25:00:00 TIME 01:00:00
24:00:00 TIME 00:00:00
2008-15-36 28:63:74.0 TIMESTAMP 2009-04-06 05:04:14.0
9999-12-31 24:00:00.0 TIMESTAMP Exception, because the adjusted value (10000-01-01 00:00:00.0) exceeds the maximum year of 9999.

The following examples demonstrate the results of retrieving data from TIMESTAMP columns in JDBC or SQLJ applications, when the values in those columns do not represent real dates or times.

Table 2. Results of retrieving DATE, TIME, or TIMESTAMP SQL values that do not represent real dates or times into Java application variables
SELECT statement Value in TIMESTAMP column TS_COL Target type in application (getXXX method for retrieval) Value retrieved from table column
SELECT TS_COL FROM TABLE1 2000-01-01 24:00:00.000000 java.sql.Timestamp (getTimestamp) 2000-01-02 00:00:00.000000
SELECT TS_COL FROM TABLE1 2000-01-01 24:00:00.000000 String (getString) 2000-01-02 00:00:00.000000
SELECT CHAR(TS_COL) FROM TABLE1 2000-01-01 24:00:00.000000 java.sql.Timestamp (getTimestamp) 2000-01-02 00:00:00.000000
SELECT CHAR(TS_COL) FROM TABLE1 2000-01-01 24:00:00.000000 String (getString) 2000-01-01 24:00:00.000000 (no adjustment by Java)

Problems with using dates in the range October 5, 1582, through October 14, 1582

The Java java.util.Date and java.util.Timestamp classes use the Julian calendar for dates before October 4, 1582, and the Gregorian calendar for dates starting with October 4, 1582. In the Gregorian calendar, October 4, 1582, is followed by October 15, 1582. If a Java program encounters a java.util.Date or java.util.Timestamp value that is between October 5, 1582, and October 14, 1582, inclusive, Java adds 10 days to that date. Therefore, a DATE or TIMESTAMP value in a Db2® table that has a value between October 5, 1582, and October 14, 1582, inclusive, is retrieved in a Java program as a java.util.Date or java.util.Timestamp value between October 15, 1582, and October 24, 1582, inclusive. A java.util.Date or java.util.Timestamp value in a Java program that is between October 5, 1582, and October 14, 1582, inclusive, is stored in a Db2 table as a DATE or TIMESTAMP value between October 15, 1582, and October 24, 1582, inclusive.

Example: Retrieve October 10, 1582, from a DATE column.

// DATETABLE has one date column with one row.
// Its value is 1582-10-10.
java.sql.ResultSet rs = 
 statement.executeQuery(select * from DATETABLE);
rs.next();
System.out.println(rs.getDate(1)); // Value is retrieved as 1582-10-20

Example: Store October 10, 1582, in a DATE column.

java.sql.Date d = java.sql.Date.valueOf("1582-10-10");
java.sql.PreparedStatement ps = 
 c.prepareStatement("Insert into DATETABLE values(?)");
ps.setDate(1, d);
ps.executeUpdate(); // Value is inserted as 1582-10-20

To retrieve a value in the range October 5, 1582, to October 14, 1582, from a Db2 table without date adjustment, execute the SQL CHAR function against the DATE or TIMESTAMP column in the SELECT statement that defines a ResultSet. Executing the CHAR function converts the date or time value to a character string value on the database side.

To store a value in the range October 5, 1582, to October 14, 1582 in a Db2 table without date adjustment, you can use one of the following techniques:
  • For a JDBC or an SQLJ application, use the setString method to assign the value to a String input parameter. Cast the input parameter as VARCHAR, and execute the DATE or TIMESTAMP function against the result of the cast. Then store the result of the DATE or TIMESTAMP function in the DATE or TIMESTAMP column.
  • For a JDBC application, set the Connection or DataSource property sendDataAsIs to true, and use the setString method to assign the date or timestamp value to the input parameter. Then execute an SQL statement to assign the String value to the DATE or TIMESTAMP column.

Example: Retrieve October 10, 1582, from a DATE column without date adjustment.

// DATETABLE has one date column called DATECOL with one row.
// Its value is 1582-10-10.
java.sql.ResultSet rs = 
 statement.executeQuery(SELECT CHAR(DATECOL) FROM DATETABLE);
rs.next();
System.out.println(rs.getString(1)); // Value is retrieved as 1582-10-10

Example: Store October 10, 1582, in a DATE column without date adjustment.

String s = "1582-10-10";
java.sql.Statement stmt = c.createStatement;
java.sql.PreparedStatement ps = 
 c.prepareStatement("Insert INTO DATETABLE VALUES " +
  "(DATE(CAST (? AS VARCHAR)))");
ps.setString(1, s);
ps.executeUpdate(); // Value is inserted as 1582-10-10 

Start of changeTo avoid date adjustment, set the SQLJ option sqljAvoidTimeStampConversion to true. The SQLJ option sqljAvoidTimeStampConversion=true returns the timestamp data in CHAR format. End of change

Start of changeExample: Retrieve October 12, 1582, from a DATE column without date adjustment.End of change

Start of change
import java.sql.*;
import javax.sql.DataSource;
import sqlj.runtime.ref.DefaultContext;
import com.ibm.db2.jcc.DBTimestamp;
import com.ibm.db2.jcc.DB2BaseDataSource;
#sql context DBContext;

public class UpdateTest 
{
// Set the timestamp conversion off.
static boolean SqljAvoidTimeStampConversion =true;
static DefaultContext ctx = null;
java.sql.Connection con;
 public static void main(String[] args) throws Exception
  { 
   javax.sql.DataSource ds = new com.ibm.db2.jcc.DB2SimpleDataSource(); 
     ((com.ibm.db2.jcc.DB2BaseDataSource) ds).setServerName 
                                             ("myserver.svl.ibm.com");
     ((com.ibm.db2.jcc.DB2BaseDataSource) ds).setPortNumber(446);
     ((com.ibm.db2.jcc.DB2BaseDataSource) ds).setDatabaseName("MYDB");
     ((com.ibm.db2.jcc.DB2BaseDataSource) ds).setDriverType(4);
     ((com.ibm.db2.jcc.DB2BaseDataSource) ds).setTimestampOutputType
                                             (DB2BaseDataSource.JCC_DBTIMESTAMP);
     ((com.ibm.db2.jcc.DB2BaseDataSource) ds).setSqljAvoidTimeStampConversion 
                                             (true); 
	   java.sql.Connection con = ds.getConnection("myid", "mypwd");
// Create a connection context instance to pass to the 
// insertAndSelectTimestamp method.
     ctx = new DefaultContext(con); 
	   System.out.println(" Default Context  Obtained Successfully...");
     insertAndSelectTimestamp(ctx);   
     }
   public static  void insertAndSelectTimestamp
           (DefaultContext ctx)throws java.sql.SQLException 
     {         
		 String temp = "";    
      #sql[ctx]  { CREATE TABLE Mytable (C1 TIMESTAMP(12))}; 
// Create the table.						 
      System.out.println ("table created"); 
      #sql[ctx]  { INSERT INTO Mytable (C1) 
                   VALUES('1582-10-12-21.22.33.123456789012') }; 
// Insert a value in the range October 5, 1582 through October 14, 1582.
// sqljAvoidTimeStampConversion is set to true so the value is stored as is.
      System.out.println ("table inserted"); 
      #sql[ctx] { COMMIT};
// Retrieve the value you inserted. sqljAvoidTimeStampConversion = true, 
// so the timestamp should not be adjusted, and you should get back the 
// value that you put in.
      #sql[ctx] { select C1 into :temp from Mytable };
      } 
}
End of change