Thanks for the question, Ruchira.
Asked: August 18, 2017 - 7:17 am UTC
Last updated: August 21, 2017 - 4:16 am UTC
Version: 12.1.0.2.0
Viewed 1000+ times
You Asked
We have 5 oracle 12C 12.1.0.2.0 databases running on 2 nodes.
on One of the Database on both nodes sysdate returns as below
select sysdate from dual;
SYSDATE
--------------------
18-AUG-2017
* on all other 4 database when sysdate was queried returns
select sysdate from dual;
SYSDATE
--------------------
18-AUG-2017 12:38:37 PM
ON the faulty database I have altered a session as below
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
result
SQL>
SYSDATE
--------------------
18-AUG-2017 00:00:00
** Only zeros are present for the time.
Can you please indicate where the error is coming from, we are baffeled because all other 4 dbs correct.
Thanks,
Ruchira.
and we said...
The display of the SYSDATE (or other DATE) result is governed either by the client tool, if it does the conversion to string itself, or by the RDBMS, if the client retrieves the date as a string. RDBMS uses the session value of the NLS_DATE_FORMAT parameter as the format to use for the conversion. I assume you run your tests in SQL*Plus. SQL*Plus does the formatting itself on the client but it synchronizes its client format with that of the server. Hence, SQL*Plus also obeys the session NLS_DATE_FORMAT setting. The default value of this setting is:
- If the NLS_LANG operating system environment variable is not defined in the environment of SQL*Plus, the default value of NLS_DATE_FORMAT is taken from the instance initialization parameters (init.ora/spfile). If the NLS_DATE_FORMAT initialization parameter is not specified explicitly in init.ora/spfile, it defaults from the NLS_TERRITORY parameter, which in turn defaults from the NLS_LANGUAGE parameter, which in turn defaults to AMERICAN. An initialization parameter is possibly used by your SQL*Plus tests.
- If the NLS_LANG operating system environment variable is defined in the environment of SQL*Plus, but the NLS_DATE_FORMAT variable is not, the default value for NLS_DATE_FORMAT is taken from the territory that is defined by NLS_LANG, e.g. AMERICA. Unspecified territory defaults from the specified language (e.g. NLS_LANG=AMERICAN means NLS_LANG=AMERICAN_AMERICA means NLS_DATE_FORMAT=DD-MON-RR)
- If the NLS_LANG operating system environment variable is defined in the environment of SQL*Plus, and the NLS_DATE_FORMAT variable is specified as well, the value of the NLS_DATE_FORMAT variable is used.
Therefore, check your environment and your initialization files.