How to make a constraint on DATE FORMAT in ORACLE

Question

say my usual Date format is '14-jan-2019' and i want my date to only be accepted as 'YYYY-MM-DD' how do i do that? and can i change jan to an actual number?

Answer 1

In my opinion, the right / correct way to do that is to declare your date column (or variable or whatever it is) as DATE, e.g.

create table test (date_column date);

or

declare
  l_date_variable date;
begin
  ...

Doing so, you'd let the database take care about valid values.

You'd then be able to enter data any way you want, using any valid date format mask, e.g.

to_date('06.01.2020', 'dd.mm.yyyy')
date '2020-01-06'
to_date('2020-06-01', 'yyyy-dd-mm')

etc. - all those values would be valid.

Answer 2

A DATE data type has no format - it is stored internally as 7-bytes representing year (2 bytes) and month, day, hour, minute and second (1 byte each).

'14-JAN-2019' is not a date - it is a text literal.

If you want to store date values then use a DATE data type.

If you want to only accept strings of a specific format as input then in whatever user interface you use to talk to the database then accept only that format. If you are converting strings to DATEs and wanting an exact format then you can use:

TO_DATE( '2019-01-14', 'fxYYYY-MM-DD' )

Note: the fx format model will mean that the exact format is expected and the typical string-to-date conversion rules will not be applied.