Oracle java date problems

[Ash More']

From JAVA the call is made to procedure as follows..

 
CallableStatement callstmt = con.prepareCall("{call Sterling_Delivery_SP(?,?,?,?,?)}");
callstmt.setInt(1,delivery.getOrderId());
System.out.println(delivery.getOrderId());
/*convert calendar date to sql date */
Date d = delivery.getDeliveryDate().getTime();
java.sql.Date sd = new java.sql.Date(d.getTime());
                                       callstmt.setDate(2,sd);     �Date inserted in procedure as 2008-08-12
                        callstmt.setString(3,delivery.getRemarks());
callstmt.setString(4, String.valueOf(status));

/* register out parameter */
callstmt.registerOutParameter(5,Types.INTEGER);

AND THIS IS MY ORACLE PROCEDURE..

create or replace procedure Sterling_Delivery_SP (
 
p_OrderId in Sterling_Delivery.OrderID%type,
p_DeliveryDate in date,  � This is the date retrieved from java.. 2008-08-12
p_Status in Sterling_Order.CourierStatus%type,
p_Remarks in Sterling_Delivery.REMARKS%type,
p_Var out number) is
  
v_Status Sterling_Order.CourierStatus%type;
v_OrderDate Sterling_Order.OrderDate%type;  
� this is the oracle database date field which  is of format 12-aug-08
 
v_ID number;
 
begin
 
if p_OrderId is NULL or p_DeliveryDate is NULL then
p_Var:=0;
return;
end if;
  
select count(*) into v_ID from Sterling_Order where OrderID=p_OrderId;
 
if v_ID=0 or v_ID is NULL then
p_var:=-1;
return;
end if;
 
  
select CourierStatus,OrderDate,OrderID into v_Status,v_OrderDate,v_ID from Sterling_Order where OrderID=p_OrderId;
 
--If status other than A and P
 
if v_Status='D' OR v_Status='R'then
p_Var:=-2;
return;
end if;
 
--If status is A
 
if p_Status='A' then
p_var:=-3;
return;
end if;
 
--If delivery date is less than orderdate
  
if p_DeliveryDate < v_OrderDate then      � I get exception here 2008-08-12 < 12-aug-08
           because both dates are different format
p_var:=-4;
return;
 
--Insertion and updation
 
else
update Sterling_Order set CourierStatus=p_Status where OrderID=p_OrderId;
insert into Sterling_Delivery values(p_OrderId,p_DeliveryDate,p_Remarks);
 
p_Var:=0;
return;
end if;
 
 
EXCEPTION
WHEN no_data_found THEN
p_Var:=-1;
 
 
WHEN others THEN
p_Var :=-5;
end;

I get exception when I compare the two dates. One comes from JAVA and the other is the ORACLE Database. So I want some function which will convert one of the dates in the other date format.
[ July 23, 2008: Message edited by: Ash More' ]

[Jan Cumps]

� I get exception here 2008-08-12 < 12-aug-08 because both dates are different format

Probably not because they are a different format. They are both Oracle DATE columns. 2008-08-12 and 12-aug-08 are merely textual represenatations of these dates.

Can you try if this works:

trunc(p_DeliveryDate) < trunc(v_OrderDate) 

[Ash More']

ok thanks,
the problem seems to be with my table structure.
thanks again