This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC 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 Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
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?
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: problem with Date comparison
 
Similar Threads
date mapping problem in Hibernet
CallableStatement problem
Oracle date-time fields
How to insert a file in oracle blob object
Working with Dates