0

How can we change the date format from DD-MON-YYYY to this format YYYY-MM-DD.

I have a date type column in a table. I want to display that value of that date column in this format - YYYY-MM-DD.

I tried with this -

disp_date := to_char(to_date(disp_date,'dd-mm-rrrr'),'rrrr-mm-dd')

and

disp_date := to_char(to_date(disp_date,'dd-mm-yyyy'),'yyyy-mm-dd')

While executing the above I got an error message stating that:

ORA-01861 Literal does not match format string

Please note the below details of my system,

select value from v$nls_parameters where parameter = 'NLS_DATE_LANGUAGE';
--AMERICAN
select value from v$nls_parameters where parameter = 'NLS_DATE_FORMAT';
--DD-MON-RRRR
MT0
  • 143,790
  • 11
  • 59
  • 117
Manivannan
  • 13
  • 2

3 Answers3

3

If column's datatype is DATE - which is what your sentence suggests:

I have a date type column in a table

then you don't to_date it - it already is a date. Just apply to_char with desired format mask, e.g.

select to_char(disp_date, 'yyyy-mm-dd') from your_table
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • 1
    Actually I tried that too, but it is not working while assigning it to the same variable again (after to_char conversion), So I declared a new variable and assigned the converted value to that, now it is working fine. Thank you so much for your response. – Manivannan Oct 08 '21 at 05:13
  • You're welcome, I'm glad you made it work. – Littlefoot Oct 08 '21 at 06:01
0

A date does not have a format - it is stored internally to the database as a binary value using 7-bytes (representing century, year-of-century, month, day, hour, minute and second). It is not until whatever user interface you are using (i.e. SQL/Plus, SQL Developer, Java, etc.) tries to display it to you, the user, that is is converted it into something you would find meaningful (usually a string) that the date is formatted (and that conversion is done by the user interface and not by the database).

How can we change one date format to another format in oracle?

Since a date does not have a format then this question does not make sense.

If instead, you ask:

How can we display a date in a format in oracle?

If you want to display the date with a specific format then you want to explicitly convert it from a date to a string using TO_CHAR (rather than relying on an implicit conversion by the user interface). Since it is already a DATE then you do not need to use TO_DATE on it and can just use:

DECLARE
  date_value     DATE := SYSDATE;
  formatted_date VARCHAR2(10);
BEGIN
  formatted_date := TO_CHAR(date_value, 'yyyy-mm-dd');
  DBMS_OUTPUT.PUT_LINE( formatted_date );
END;
/

Now, if your disp_date variable is a string (and not a date) then your code works:

DECLARE
  disp_date VARCHAR2(11) := TO_CHAR(SYSDATE, 'DD-MON-RRRR');
BEGIN
  disp_date := TO_CHAR(TO_DATE(disp_date, 'DD-MON-RRRR'), 'yyyy-mm-dd');
  DBMS_OUTPUT.PUT_LINE( disp_date );
END;
/

db<>fiddle here

MT0
  • 143,790
  • 11
  • 59
  • 117
  • Thank you so much for your in depth explanation and answer. Yes, Actually later I tried to assign the converted value into another string type variable and it is working fine. Thanks a lot! – Manivannan Oct 08 '21 at 05:19
0

If you want to change the default display format then run

alter session set nls_date_format = 'YYYY-MM-DD';
select disp_date from ...

Note, your client application may change the format again according to settings in this client application.

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • If the client application is not SQL/Plus or SQL Developer (and maybe a limited handful of others) then this is not likely to affect how the client displays date data types as that is going to be controlled by a parameter on the client application and not a session parameter in the database. – MT0 Oct 07 '21 at 11:35
  • I have assigned the converted value into another variable and now it is working fine. Thanks for your response. – Manivannan Oct 08 '21 at 05:20