I want all my dates in my database in the following yyyy-mm-dd date format. For example, ‘2020-05-28’. But this doesn’t seem to work as I thought it would when I insert a date into my User table which contains the birthday column.
Here’s my birthday column constraint :
birthday DATE NOT NULL CHECK (REGEXP_LIKE(birthday , 'd{4}-(0?[1-9]|1[012])-(0?[1-9]|[12][0-9]|3[01])*')),
I get the following error when I try to insert : ‘2020-05-28’ or any other date for that matter :
02290. 00000 - "check constraint (%s.%s) violated" *Cause: The values being inserted do not satisfy the named check *Action: do not insert values that violate the constraint.
Advertisement
Answer
You are wrongly interpreting the concept of the DATE datatype. Probably, you are mixing the display format of dates (the value that you see when you display a date value), and their internal representation.
A date is just that: a date. It is stored internally in some database specific format, that you don’t need to worry about. So there is no need for a check constraint to control the format.
Whenever you need to display date, you can use function to_char() to format it as a string, like:
to_char(birthday, 'yyyy-mm-dd')
You can also set session parameter NLS_DATE_FORMAT so all dates are automatically formated in a given format when displayed:
alter session set nls_date_format = 'yyyy-mm-dd';