how can we change one date format to another format in oracle sql? ORA-01861-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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.