changing date structure for oracle sql

Question

I'm trying to modify an already existing date from ('DD,MON,YY') to ('DD,MONTH,YYYY') in oracle SQL but it's not working with me

ALTER TABLE EMP MODIFY (hiredate to_date ('DD,MON,YYYY'))

Answer 1

This is a common misconception that dates in the database have a format.

You cannot modify the format of a DATE data type value as it does not have a format; it is stored in the database as 7 bytes: 2 for year, and 1 each for month, day, hour, minute and second and always has those components and they are just numbers. The database doesn't care about format; it only cares about values.

The client software you are using to access the database cares about formats as it has to talk to you, the user, and you don't typically like being given lots of numbers and the client software will try to format the date for you.

If instead you want to ask:

How do we get <insert name of SQL client software here> to change the default format it uses to display a DATE data type?

For SQL/Plus and SQL Developer, you can use the NLS_DATE_FORMAT session parameter:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD,MONTH,YYYY';

If you are using different software to talk to the database then you will need to read the manual/documentation for that software.

Alternatively, if you want to ask:

How do we display DATE values from a table with a specific format?

Use SELECT with the TO_CHAR function:

SELECT TO_CHAR( date_column, 'DD,MONTH,YYYY' ) AS formatted_date_column
FROM   table_name;