I am working with a legacy Oracle database that has columns containing date strings formatted as follows:
30-Apr-03
30-Apr-2003
The column data type is VARCHAR2(12) (although 11 would suffice). Both of these representations can appear in a single column.
Question: How can I write a CHECK constraint that permits only these two date string variants in a column?
Here is a CHECK constraint to validate date strings of the form 19-Oct-12 or 19-Oct-2012. In the case of date strings with a two-digit year, use a RegEx to prefix the year with 19 to avoid Oracle error ORA-02436 - date or system variable wrongly specified in CHECK constraint.
ALTER TABLE table1
MODIFY col_date1 CONSTRAINT col_date1_ck
CHECK ((TO_DATE(
REGEXP_REPLACE(col_date1, '^(......-)(..)$', '\119\2'),
'DD-Mon-YYYY') > TO_DATE('31-Dec-1899')));
Longer term, if it is acceptable to make changes to the schema, using the DATE data type (11g Release 2 (11.2)) would be more appropriate.
UPDATE: Unfortunately, this is not an effective solution. First, here is a situation where this CHECK constraint works as intended:
Value: '05/Nov/12'
Result: ORA-02290: check constraint (TABLE1.COL_DATE1_CK) violated
However, this date string is accepted by the constraint:
Value: '05/Nov/2012'
This is not wanted since the intent is to use '-' as the field separator, not '/'. Finally, consider this date string and its result:
Value: '05/Foo/2012'
Result: ORA-01843: not a valid month
Here also, the constraint will not allow this value into the protected column. However, there is no indication in the resulting exception that this was a constraint violation. (What is wanted is ORA-02290 as shown in the first example. But TO_DATE() is returning an exception instead of the CHECK constraint returning False.)
Alternative: For these reasons, I ultimately decided to solve this problem via a trigger. But I certainly welcome ideas on revising this CHECK constraint so that it truly accomplishes the objective.