Skip to Main Content
  • Questions
  • SQL TO_CHAR returns differently between 2 Servers

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Connor McDonald

Thanks for the question, Jean-Philippe.

Asked: July 06, 2020 - 12:52 pm UTC

Answered by: Connor McDonald - Last updated: July 22, 2020 - 5:24 am UTC

Category: SQL - Version: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Viewed 100+ times

You Asked

Hello,
I have two server with version 12.2.0.1.0.

When I run the same SQL on each I don't have the same result on dates when I used then with TO_CHAR...

SQLPLUS:
SELECT CONTRACTDATE, TO_CHAR(CONTRACTDATE,'YYYY/MM/DD') FROM MYTABLES WHERE CONTRACTNO=101010;


COLUMN CONTRACTDATE is a DATE.

SERVER1
CONTRACTD TO_CHAR(CO
--------- ----------
05-DEC-19 2019/12/05

SERVER2
CONTRACTD TO_CHAR(CO
--------- ----------
05-DEC-19 0019/12/05

As you can see on the second server it use 0019 for the year instead of the expected 2019

Any idea why I have such behaviour?
Thank you

PS: I have checked the NLS and they are identical.
SELECT DB.PARAMETER, DB.VALUE "DATABASE", I.VALUE "INSTANCE", S.VALUE "SESSION"
FROM   NLS_DATABASE_PARAMETERS DB, NLS_INSTANCE_PARAMETERS I, NLS_SESSION_PARAMETERS S
WHERE  DB.PARAMETER=I.PARAMETER(+) AND DB.PARAMETER=S.PARAMETER(+)
ORDER BY 1;


and we said...

Yes - the date was most likely entered incorrectly, ie, it assumed a particular format mask rather than used an explict to_date

SQL> create table t ( id int, x date );

Table created.

SQL>
SQL> alter session set nls_date_format = 'DD-MON-RR';

Session altered.

SQL> insert into t values ( 1,'01-JUL-20');

1 row created.

SQL> select id, to_char(x,'YYYY-MM-DD') from t;

        ID TO_CHAR(X,
---------- ----------
         1 2020-07-01

SQL>
SQL> alter session set nls_date_format = 'DD-MON-YYYY';

Session altered.

SQL> insert into t values (2,'01-JUL-20');

1 row created.

SQL> select id, to_char(x,'YYYY-MM-DD') from t;

        ID TO_CHAR(X,
---------- ----------
         1 2020-07-01
         2 0020-07-01


and you rated our response

  (2 ratings)

Reviews

Possible Correction

July 20, 2020 - 2:41 pm UTC

Reviewer: Jean-Philippe from Belgium

Hello,
Is there a way to "correct" the values? Forcing to the Server NLS all the dates.
I tried to force the NLS to RR then update contractdate=contractdate but without success.



UPDATE to solve the issue

July 21, 2020 - 7:33 am UTC

Reviewer: jean-philippe from Belgium

update T
set
CONTRACTDATE=to_date(
to_char( to_date( CONTRACTDATE, 'dd-mm-rr' ), 'YYYY' ) || '/' || to_char( to_date( CONTRACTDATE, 'dd-mm-rr' ), 'MM/DD' )
,'YYYY/MM/DD')
where
CONTRACTDATE IS NOT NULL;


Connor McDonald

Followup  

July 22, 2020 - 5:24 am UTC

Hmmmmm

SQL> create table t ( id int, x date );

Table created.

SQL> alter session set nls_date_format = 'DD-MON-RR';

Session altered.

SQL> insert into t values ( 1,'01-JUL-20');

1 row created.

SQL> select id, to_char(x,'YYYY-MM-DD') from t;

        ID TO_CHAR(X,
---------- ----------
         1 2020-07-01

SQL> alter session set nls_date_format = 'DD-MON-YYYY';

Session altered.

SQL> insert into t values (2,'01-JUL-20');

1 row created.

SQL> select id, to_char(x,'YYYY-MM-DD') from t;

        ID TO_CHAR(X,
---------- ----------
         1 2020-07-01
         2 0020-07-01

SQL>
SQL> update T
  2  set
  3  x=to_date(to_char( to_date( x, 'dd-mm-rr' ), 'YYYY' ) || '/' || to_char( to_date( x, 'dd-mm-rr' ), 'MM/DD' ),'YYYY/MM/DD')
  4  where x IS NOT NULL;

2 rows updated.

SQL> select id, to_char(x,'YYYY-MM-DD') from t;

        ID TO_CHAR(X,
---------- ----------
         1 2020-07-01
         2 0020-07-01



Maybe something like this


SQL>
SQL> create table t ( id int, x date );

Table created.

SQL> alter session set nls_date_format = 'DD-MON-RR';

Session altered.

SQL> insert into t values ( 1,'01-JUL-20');

1 row created.

SQL> select id, to_char(x,'YYYY-MM-DD') from t;

        ID TO_CHAR(X,
---------- ----------
         1 2020-07-01

SQL> alter session set nls_date_format = 'DD-MON-YYYY';

Session altered.

SQL> insert into t values (2,'01-JUL-20');

1 row created.

SQL> select id, to_char(x,'YYYY-MM-DD') from t;

        ID TO_CHAR(X,
---------- ----------
         1 2020-07-01
         2 0020-07-01

SQL>
SQL> alter session set nls_date_format = 'DD-MON-RR';

Session altered.

SQL> insert into t values ( 3,'01-JUL-95');

1 row created.

SQL> select id, to_char(x,'YYYY-MM-DD') from t;

        ID TO_CHAR(X,
---------- ----------
         1 2020-07-01
         2 0020-07-01
         3 1995-07-01

SQL> alter session set nls_date_format = 'DD-MON-YYYY';

Session altered.

SQL> insert into t values (4,'01-JUL-95');

1 row created.

SQL> select id, to_char(x,'YYYY-MM-DD') from t;

        ID TO_CHAR(X,
---------- ----------
         1 2020-07-01
         2 0020-07-01
         3 1995-07-01
         4 0095-07-01

SQL>
SQL> update T
  2  set x = add_months(x,2000*12)
  3  where x between date '0001-01-01' and date '0030-01-01';

1 row updated.

SQL>
SQL> update T
  2  set x = add_months(x,1900*12)
  3  where x between date '0031-01-01' and date '0100-01-01';

1 row updated.

SQL>
SQL> select id, to_char(x,'YYYY-MM-DD') from t;

        ID TO_CHAR(X,
---------- ----------
         1 2020-07-01
         2 2020-07-01
         3 1995-07-01
         4 1995-07-01

SQL>
SQL>
SQL>


More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.