Skip to Main Content

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Question and Answer

Sergiusz Wolicki

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:

  1. 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.

  2. 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)

  3. 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.


We're not taking comments currently, so please try again later if you want to add a comment.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.