CODEWITHSUNDEEP
oracle-databasesqlplus
PatPrograms
PatPrograms

Reputation: 13

How to format a Column with date datatype to YYYY-MM-DD

I am creating a new table and I need a column with datatype DATE to have format YYYY-MM-DD when the column is initialized. I have used the CHECK TO_DATE() but I am getting an error message, which could come from the way I am entering the value within the table?

I have tried my_date DATE CHECK (my_date = TO_DATE(my_date,'YYYY-MM-DD')

CREATE TABLE my_table(mytable_id VARCHAR(2) PRIMARY KEY
          my_date DATE NOT NULL
          CHECK (my_date = TO_DATE(my_date,'YYYY-MM-DD')));

INSERT INTO my_table VALUES('01','2019-09-28');
SELECT * FROM my_table;

I expected my columns and rows but instead, it is giving me an error message ORA-01861: literal does not match the format string. I have tried several ways but nothing work. Thank you for your help.

Upvotes: 1

Views: 1336

Answers (1)

dani herrera
dani herrera

Reputation: 51665

In your schema my_date is already a date, you can't convert a date to date, this line has no sense:

my_date DATE NOT NULL
CHECK (my_date = TO_DATE(my_date,'YYYY-MM-DD')));

You can write to_date to cast string to a date:

INSERT INTO my_table VALUES('01',TO_DATE('2019-09-28'));

You should to understand that Oracle doesn't store date in string format, Oracle stores date in an internal binary format, because this, you should to cast from string to the internal binary representation for date.

If you want to format date on output, the way is use to_char:

select to_char( my_date, 'YYYY-MM-DD' ) as myFormatedDate
from my_table;

Upvotes: 4

Related Questions