Search code examples
phporacledatesessionnls-lang

Oracle NLS_TERRITORY overrides NLS_DATE_FORMAT

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

Solution

  • 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