Here's what I had to do:
((OraclePreparedStatement)stmt).setDATE(2, new oracle.sql.DATE(new java.sql.Timestamp(startDate.getTime())));
First, I needed to cast to OraclePreparedStatement to have access to the setDATE() function (as opposed to PreparedStatement.setDate()). The setDATE() function takes the crucial oracle.sql.DATE object that maps between JDBC SQL types and the Oracle DATE SQL type. Think of it this way; I can instantiate oracle.sql.DATE with a java.sql.Date, Timestamp, Time, String or other object, and it will try to map those objects to the Oracle DATE SQL type. In this case, I needed an object that preserved the time information along with the date. Therefore, I instantiated the oracle.sql.DATE object with java.sql.Timestamp. Had I instantiated the DATE object with java.sql.Date, it may have succeeded, but time information would be zeroed out.
An alternative to the previous method would be to map a java.sql.Timestamp object to the DATE field using the following:
stmt.setTimestamp(2, new java.sql.Timestamp(startDate.getTime()));I am to understand from the Oracle JDBC FAQ that since Oracle 9.2, the Oracle DATE SQL type has been mapped to java.sql.Date. The FAQ calls attention to the fact that there will be conflicts if you depend on time information in a DATE field. The above methods both work with Oracle 184.108.40.206.0, so I am not sure what aspect of functionality will be impacted.
Below is a quick summary:
- java.util.Date - Holds date and time information; really of no use here other than to get the date/time into the method.
- java.sql.Date - Corresponds to a generic JDBC DATE SQL type. Holds only the date part (2007-06-06).
- java.sql.Timestamp - Corresponds to a generic JDBC TIMESTAMP SQL type. Holds date AND time (2007-06-06 02:32:04).
- Oracle SQL DATE type - Stores date and time information, but no nanoseconds. Previous to Oracle 9.2, mapped to java.sql.Timestamp. Post 9.2, maps to java.sql.Date.
- Oracle SQL TIMESTAMP type - New in Oracle 9.2. Stores date and time information; includes nanoseconds. Since Oracle 9.2, mapped to java.sql.Timestamp.
- oracle.sql.DATE - Corresponds to the Oracle DATE SQL type. Holds date and time, but in order to function properly, should be instantiated with a java.sql.Timestamp object that also holds date and time.
- PreparedStatement.setDate(int, java.sql.Date) - Set a JDBC DATE SQL type field in the prepared statement. Only preserves the date part.
- PreparedStatement.setTimestamp(int, java.sql.Timestamp) - Set a JDBC TIMESTAMP SQL type field in the prepared statement. Preserves date and time, and can be tricked into setting an Oracle DATE type field with date and time information.
- OraclePreparedStatement.setDATE(int, oracle.sql.DATE) - Set an Oracle DATE SQL type field in the prepared statement. Preserves date and time. In my opinion, the most intuitive method (once you understand it) to set Oracle DATE fields in prepared statements.