problem with Date comparison

[shuangquan zhang]

Strange problem with Oracle Date/Java (I am using Oracle server 8.1.7 and Java 1.2.2):
I am currently working with Oracle and Java. In my application I will insert into a 'plan' table a few plan, each plan has a plan name, starting date, a ending date, a memo. When I run this application, it should tell me which plans I should be currently working on.
/* database schema */
CREATE TABLE PLAN (
name VARCHAR2(20),
from_date DATE NOT NULL,
to_date DATE NOT NULL,
memo VARCHAR2(256)
);
create or replace trigger TI_PLAN_TO_DATE
BEFORE INSERT
on PLAN

for each row
begin
IF (:new.TO_DATE IS NULL) THEN
/* default to 50 years later */
:new.TO_DATE := :new.FROM_DATE + 18250;
END IF;
end;
/
/* end of database schema */
/* Java code */

import oracle.sql.*;
import oracle.jdbc.driver.*;
import java.sql.*;
import java.util.*;
public class sqlDate {
static {
try {
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
} catch (SQLException e) {
e.printStackTrace();
}
}

public static void main(String[] args){
String user = "CNC", passwd = "CNC";
String net_service_name = "SAMZ";
String url="jdbc racle ci8:@" + net_service_name;

String sql_live_plan = "SELECT name, from_date, to_date, memo from PLAN where to_date(from_date) <= ? AND to_date(to_date) >= ? ";

try {
// query for live plans
Connection conn = DriverManager.getConnection (url, user, passwd);
OraclePreparedStatement ps_live_plan = (OraclePreparedStatement)conn.prepareStatement(sql_live_plan);

Timestamp t = new Timestamp(System.currentTimeMillis());
ps_live_plan.setTimestamp(1,t);
ps_live_plan.setTimestamp(2,t);

OracleResultSet rs_live_plan = (OracleResultSet)ps_live_plan.executeQuery();
for(int i=0; rs_live_plan.next(); i++) {
System.out.println("Plan "+i+" to work on: " + rs_live_plan.getString(1) +
" from " + rs_live_plan.getDate(2) +
" to " + rs_live_plan.getDate(3) +
" Memo " + rs_live_plan.getString(4) );
}
rs_live_plan.close();
ps_live_plan.close();
} catch (SQLException e) {
System.out.println("Error querying for live plan\n" + e);
e.printStackTrace();
}
System.exit(0);
}
}

/* end of java code */
when I ran my java code after "insert into plan (name, from_date) values ('test 1', '07-may-01');" and committed, I got no live plan printed.
when I ran my java code after "insert into plan (name, from_date, to_date) values ('test 2', '07-may-01', '01-jun-01');" and committed, I got an "test 2" printed.
But why? Any expert can explain this for me?

[Daniel Dunleavy]

Did you check the rows in the table, are the to and from dates correct?
Dan

[shuangquan zhang]

Yes, they are there, correct. When I query from the sqlplus command, such as " where from_date >= to_date('07-may-01') and to_date <= to_date('07-may-01') " , I get the expected result.
But it is just not right querying from the java code.
Will you please help me out?