Home » SQL & PL/SQL » SQL & PL/SQL » Expiration date field constraint.
Expiration date field constraint. [message #21398] Thu, 01 August 2002 05:27 Go to next message
Gautham Sunkara
Messages: 2
Registered: July 2002
Junior Member
Hi
I had to have a contraint of the expiration date that
If the expiration date is older than the present date I should not retrieve that record.

I used the following statement for implement that constraint

TO_CHAR(ppp.expiration_date, 'MM/DD/YYYY') > TO_CHAR(SYSDATE, 'MM-DD-YYYY')

The problem is I am still getting records retrieved which violate the above constraint.
Can anyone sort this problem or is there any better way to implement it.

Thanks,
Gautham.
Re: Expiration date field constraint. [message #21400 is a reply to message #21398] Thu, 01 August 2002 06:12 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
You directly use
select * from emp where hiredate < 'yourdate';

if want to do some date formating use
select * from emp where
TO_DATE(hiredate,'DD-MON-YYYY') <  TO_DATE('03-DEC-81','DD-MON-YYYY')

Re: Expiration date field constraint. [message #21408 is a reply to message #21398] Thu, 01 August 2002 10:13 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
You can't reference sysdate in a check constraint. Try a trigger.
create table date_tab (date_col date);

Table created

CREATE OR REPLACE TRIGGER date_tab_biu
   BEFORE INSERT OR UPDATE
   ON date_tab
   FOR EACH ROW
BEGIN
   IF :NEW.date_col >= SYSDATE
   THEN
      raise_application_error (-20501, 'ERROR: DATE_COL must be < sysdate');
   END IF;
END;
/

Trigger created

insert into date_tab values(sysdate - 1)

1 row inserted

insert into date_tab values(sysdate);

ORA-20501: ERROR: DATE_COL must be < sysdate
ORA-06512: at "SCOTT.DATE_TAB_BIU", line 4
ORA-04088: error during execution of trigger 'SCOTT.DATE_TAB_BIU'

insert into date_tab values(sysdate + 1);

ORA-20501: ERROR: DATE_COL must be < sysdate
ORA-06512: at "SCOTT.DATE_TAB_BIU", line 4
ORA-04088: error during execution of trigger 'SCOTT.DATE_TAB_BIU'
Previous Topic: Couldnot create synonym for snapshot
Next Topic: sql ?
Goto Forum:
  


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