| Comparing two dates [message #36475] |
Thu, 29 November 2001 05:58  |
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  |
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 |
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 |
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'
----------------------------------------------------------------------
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
