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

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Jean-Philippe.

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

Last updated: July 22, 2020 - 5:24 am UTC

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

Viewed 1000+ 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 Connor 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


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Possible Correction

Jean-Philippe, July 20, 2020 - 2:41 pm UTC

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

jean-philippe, July 21, 2020 - 7:33 am UTC

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
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 demos

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

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library