Oracle NLS_TERRITORY overrides NLS_DATE_FORMAT

Question

When in PHP application I specify

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
ALTER SESSION SET NLS_DATE_LANGUAGE = 'RUSSIAN';

the output data format is as expected 2020-01-31 21:21:47 But when I add

ALTER SESSION SET NLS_TERRITORY = 'CIS';

NLS_DATE_FORMAT doesn't take effect, NLS_TERRITORY overrides it. 31.01.20

Answer 1

The nls_date_format (amongst other settings) is derived from the nls_territory. So when you set the territory, the database also sets the date format to the default for this area:

select value from nls_session_parameters
where  parameter = 'NLS_DATE_FORMAT';

VALUE
--------------------------------------------------------------------------------
DD-MON-RR

ALTER SESSION SET NLS_TERRITORY = 'CIS';

select value from nls_session_parameters
where  parameter = 'NLS_DATE_FORMAT';

VALUE
--------------------------------------------------------------------------------
DD.MM.RR

So you need to set the date format after the territory:

ALTER SESSION SET NLS_TERRITORY = 'CIS';
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
ALTER SESSION SET NLS_DATE_LANGUAGE = 'RUSSIAN';
select value from nls_session_parameters
where  parameter = 'NLS_DATE_FORMAT';

VALUE
--------------------------------------------------------------------------------
YYYY-MM-DD HH24:MI:SS

Or - better still - use an explicit format mask in your conversions:

ALTER SESSION SET NLS_TERRITORY = 'CIS';
select sysdate, to_char ( sysdate, 'YYYY-MM-DD HH24:MI:SS' ) from dual;

SYSDATE  TO_CHAR(SYSDATE,'YY
-------- -------------------
07.02.20 2020-02-07 09:25:35

Answer 2

Oracle's default date format depends on the NLS_TERRITORY setting. So when you set the NLS_TERRITORY you are also telling Oracle to reset the NLS_DATE_FORMAT (and other similar parameters) to the defaults for the new territory.

From the Oracle Documentation:

1.206 NLS_TERRITORY

NLS_TERRITORY specifies the name of the territory whose conventions are to be followed for day and week numbering.

...

This parameter also establishes the default date format, the default decimal character and group separator, and the default ISO and local currency symbols.

For information on these settings, see "NLS_DATE_FORMAT", "NLS_NUMERIC_CHARACTERS", "NLS_CURRENCY", and "NLS_ISO_CURRENCY".

If you want to change the territory and the date language & format then you will need to change the territory first (which will implicitly change the values for the NLS_DATE_FORMAT, etc. to the territory's defaults) and then you can change the date language & format to override those default territory settings:

So:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
ALTER SESSION SET NLS_DATE_LANGUAGE = 'RUSSIAN';
ALTER SESSION SET NLS_TERRITORY = 'CIS';

should be:

ALTER SESSION SET NLS_TERRITORY = 'CIS';
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
ALTER SESSION SET NLS_DATE_LANGUAGE = 'RUSSIAN';

Answer 3

In addition to the other answers, you should combine the settings into a single ALTER SESSION statement:

ALTER SESSION SET NLS_TERRITORY = 'CIS' NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS' NLS_DATE_LANGUAGE = 'RUSSIAN';

This is particularly recommended if you are executing the statement from PHP (and not just via a PL/SQL block or logon trigger) since it reduces the number of round-trips between PHP and the database, and can have a significant performance benefit.

For discussion of using triggers, see p 304 of the free Oracle PDF The Underground PHP and Oracle Manual.

If you do need to execute the statement from PHP, and have other SQL commands that are executed at logon, wrap them all in an anonymous PL/SQL block:

begin
     execute immediate
       'alter session set nls_date_format = ''YYYY-MM-DD'' nls_language = AMERICAN';
     -- other SQL statements could be put here
end;

This can be executed with one oci_parse() call, so it only needs one round-trip.