check constraint for day and month only in Oracle SQL

Question

So I need a constraint that will prevent a user from entering a day/month other than the first day of the quarter and last day of the quarter, as well as a date that the table will be 'locked' for editing. I did some searching online, and thought I found the answer, but when I tried it I got an error.

alter table TABLE_NAME
    add constraint data_beg_dt_chk check 
(trunc(data_beg_dt, 'DD-MON') = (trunc(to_date('01-JAN', 'DD-MON'))||(trunc(sysdate, 'YYYY'))) 
    OR trunc(data_beg_dt, 'DD-MON') = (trunc(to_date('01-APR', 'DD-MON'))||(trunc(sysdate, 'YYYY'))) 
    OR trunc(data_beg_dt, 'DD-MON') = (trunc(to_date('01-JUL', 'DD-MON'))||(trunc(sysdate, 'YYYY')))
    OR trunc(data_beg_dt, 'DD-MON') = (trunc(to_date('01-OCT', 'DD-MON'))||(trunc(sysdate, 'YYYY'))));

I get the error:

SQL Error: ORA-02436: date or system variable wrongly specified in CHECK constraint
02436. 00000 -  "date or system variable wrongly specified in CHECK constraint"

*Cause:    An attempt was made to use a date constant or system variable,
           such as USER, in a check constraint that was not completely
           specified in a CREATE TABLE or ALTER TABLE statement.  For
           example, a date was specified without the century.

*Action:   Completely specify the date constant or system variable.
           Setting the event 10149 allows constraints like "a1 > '10-MAY-96'",
           which a bug permitted to be created before version 8.

Does anyone know another way I can use just the day and month, but at the same time keep the current year that the user has enetered?

Thanks

John Bollinger · Answer 1 · 2015-04-08T16:34:42.700

You cannot have a date or datetime without a year, but you're trying to create such dates with the to_date() function. Dates are their own data type. You cannot create partial dates and concatenate them as if they were strings (though you could do that with actual char or varchar data). Moreover, you're making it a lot harder than it needs to be. Oracle's EXTRACT() function can extract day and / or month numbers from dates, and that's what you really want, especially given that you can do math on month numbers.

For example:

alter table TABLE_NAME
  add constraint data_beg_dt_chk
    check EXTRACT(day from data_beg_dt) = 1
      and MOD(EXTRACT(month from data_beg_dt), 3) = 1;

Note that unlike the constraint you tried to write, the above does not constrain the dates to be in the current year. If Oracle even accepted such a constraint, it would result in a table in which every new year, every row would go from obeying the constraint to violating it, without any data having changed.

If you want the database to prevent the table from being modified outside of a certain date range, or if you want to restrict changes to certain rows based on the current date, then you should look into triggers instead of table constraints for that purpose.

check constraint for day and month only in Oracle SQL

1 Answers1