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:
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'.
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.
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.
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) |
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.
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
To avoid date adjustment, set the SQLJ option sqljAvoidTimeStampConversion to true. The SQLJ option sqljAvoidTimeStampConversion=true returns the timestamp data in CHAR format.
Example: Retrieve October 12, 1582, from a DATE column without date adjustment.
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 };
}
}