aspose file tools
The moose likes JDBC and Relational Databases and the fly likes problem with Date comparison Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Groovy Fundamentals video training course this week in the Groovy forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "problem with Date comparison" Watch "problem with Date comparison" New topic
Author

problem with Date comparison

shuangquan zhang
Greenhorn

Joined: Aug 24, 2000
Posts: 7
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
Ranch Hand

Joined: Mar 13, 2001
Posts: 276
Did you check the rows in the table, are the to and from dates correct?
Dan
shuangquan zhang
Greenhorn

Joined: Aug 24, 2000
Posts: 7
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?
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: problem with Date comparison