Different date format cause due to NLS_LANG parameter in Oracle

Different Data format due to different NLS_TERRITORY in Oracle

In following example, we show you the different NLS_LANG setting for DB Server or Client Which will show you the different date format representation at their connection.

On Server, Set the NLS_LANG = AMERICAN_AMERICA.WE8MSWIN1252
On Client, Set the NLS_LANG = JAPANESE_JAPAN.JA16SJIS.

Client Connection
We connect through the client binaries to the database server where parameter NLS_LANG in regedit change to JAPANESE_JAPAN.JA16SJIS

Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
SQL> select sysdate from dual;
SYSDATE
--------
21-03-28   --- Show date format RR-MM-DD

SQL> set line 200 pages 200
SQL> Col parameter for a20
SQL> col value for a20
SQL> select parameter,value from V$NLS_PARAMETERS where parameter in ('NLS_LANGUAGE','NLS_TERRITORY','NLS_CHARACTERSET');

PARAMETER            VALUE
-------------------- --------------------
NLS_LANGUAGE         JAPANESE
NLS_TERRITORY        JAPAN
NLS_CHARACTERSET     AL32UTF8

From DB Server Connection
We connect directly using database binaries where NLS_LANG in registry is default AMERICAN_AMERICA.WE8MSWIN1252

Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

SQL> select sysdate from dual;
SYSDATE
---------
28-MAR-21   -- Show date format DD-MON-RR

SQL> Col parameter for a20
SQL> col value for a20
SQL> select parameter,value from V$NLS_PARAMETERS where parameter in ('NLS_LANGUAGE','NLS_TERRITORY','NLS_CHARACTERSET');

PARAMETER            VALUE
-------------------- --------------------
NLS_LANGUAGE         AMERICAN
NLS_TERRITORY        AMERICA
NLS_CHARACTERSET     AL32UTF8

Check client connectivity with different charset to DB server.

SQL> select distinct sid,client_charset from v$session_connect_info;

     SID CLIENT_CHARSET
---------- ----------------------------
       103 JA16SJIS
       202 WE8MSWIN1252

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.