Cyntech’s Tech Blog

I spent a few days wrestling with Dates with Oracle and Java this week. I thought I’d post my findings here to save others the trouble.

When using PreparedStatement objects to interface with the Database, using PreparedStatement.setDate( int parameter, java.sql.Date field ) will truncate the time off the date.

If you require the time as well as the date, use java.sql.Timestamp.

For example:

String query = "select EVENT, EVENT_DATE from SAKAI_EVENT where EVENT_DATE > ?";
java.util.Date now = new java.util.Date();  // Sets to now
java.util.Date eventDate;
String event;

try {
    PreparedStatement ps = conn.prepareStatement( query );
    ps.clearParameters();

    ps.setTimestamp( 1,  new java.sql.Timestamp( now.getTime() ) );

    ResultSet rs = ps.executeQuery();
    while (rs.next()) {
        event = rs.getString( "EVENT" );
        eventDate = new java.util.Date( rs.getTimestamp( "EVENT_DATE" ).getTime() );
    }
    ps.close();

} catch (SQLException e) {
    throw new Exception( "There was an error executing the query: " + query, e );
}

This code block shows how you can set the Date in the bind variable with a timestamp, similarly obtain a date, with the time intact, from a timestamp.