Home » SQL & PL/SQL » SQL & PL/SQL » Comparing two dates
Comparing two dates [message #36475] Thu, 29 November 2001 05:58 Go to next message
Maria
Messages: 57
Registered: August 1999
Member
Hello!
I have two columns of type Date:
from_date DATE
to_date DATE
and I want to compare them.
Specifically I want to create a row trigger after insert or update of column to_date, that would raise an error if the to_date value is greater than from_date value.
I have inserted in database the date values in the following format :
Insert into my_table(from_date,to_date) values(to_date(
'11-11-2000',DD-MM-YYYY), to_date('11-11-2002',DD-MM-YYYY)).
I am comparing them now wrongly,
because rows where
to_date < from_date
are created(and i don't want that) )
Any help would be grateful
thank you!

----------------------------------------------------------------------
Re: Comparing two dates [message #36477 is a reply to message #36475] Thu, 29 November 2001 06:41 Go to previous messageGo to next message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
create or replace trigger trig1
before insert on tablename
for each row
begin
if :new.from_date>:new.to_date then
raise_Application_error(-20099,'to_date should be greater than from_date');
end if;
end;

----------------------------------------------------------------------
Re: Comparing two dates [message #36482 is a reply to message #36477] Thu, 29 November 2001 07:33 Go to previous messageGo to next message
Rob Baillie
Messages: 33
Registered: November 2001
Member
Maybe I'm being pedantic, but your test and your error message don't match.

You're testing for from>to, and reporting from must be<to.

If it's equal then it will pass, when your error message suggests it shouldn't...

----------------------------------------------------------------------
Re: Comparing two dates [message #36484 is a reply to message #36477] Thu, 29 November 2001 08:00 Go to previous message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
yes i agree, have to change condition '>=' or message to 'to_date should be greater than or equal to from_date'

----------------------------------------------------------------------
Previous Topic: Re: Mutating Trigger...More thoughts
Next Topic: dbms_output.put_line buffer limit
Goto Forum:
  


Current Time: Fri Mar 29 08:05:23 CDT 2024