pl/sql field formatting

[mxd198]

I was wondering if there is a way that you can have a client enter in a varchar field a date in the format of 20051101 only. Is there something in the table column that can be set, like the default or something like that. If this format is not entered this way then my procedure will say it cannot recognize this as a date. I tried making it a date field. Some how this was the only way I could get it to work.

please help.

thanks in advance!

[olivthill]

You can use a trigger "BEFORE INSERT OR UPDATE" of your field.
Or you can add a "CHECK CONSTRAINT", e.g.:
CREATE TABLE foo
(fielddate VARCHAR2(8)
CONSTRAINT check_dateyyyymmdd
CHECK ( (TO_NUMBER(SUBSTR(fielddate, 1, 4)) BETWEEN 1900 AND 2099)
AND (TO_NUMBER(SUBSTR(fielddate, 5, 2)) BETWEEN 1 AND 12)
AND (TO_NUMBER(SUBSTR(fielddate, 7, 2)) BETWEEN 1 AND 31))
fieldother .....
)

[mxd198]

I think I will try the constraint with the table. That is more of what I am looking for. So that will be in the table when it is created, right? So, when they enter a date in that field of the table, they will only be able to enter, let's say, 20051101. thanks!

[olivthill]

The constraint is added when the table is created, or, theoretically, the constraint can be added later with
ALTER TABLE table
MODIFY column datatype CONSTRAINT column-constraint;I suppose, it is:ALTER TABLE foo
MODIFY fielddate VARCHAR2(8)
CONSTRAINT check_dateyyyymmdd
CHECK ( (TO_NUMBER(SUBSTR(fielddate, 1, 4)) BETWEEN 1900 AND 2099)
AND (TO_NUMBER(SUBSTR(fielddate, 5, 2)) BETWEEN 1 AND 12)
AND (TO_NUMBER(SUBSTR(fielddate, 7, 2)) BETWEEN 1 AND 31));