Skip to Main Content
  • Questions
  • nls_date_format difference between v$parameter and database_properties

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Curious.

Asked: December 13, 2017 - 5:01 pm UTC

Last updated: December 15, 2017 - 6:38 am UTC

Version: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

Viewed 1000+ times

You Asked

When I query v$parameter, nls_date_format is YYYY-MM-DD

When I query database_properties, nls_date_format is DD-MON-RR

Why is it different? Could this cause problems?

and Connor said...

database_properties are the permanent properties, ie, those used when the database was created.

v$parameter represents those in use in the current instance (or current session), eg

SQL> select value from v$parameter where name = 'nls_date_format';

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

SQL> alter session set nls_date_format = 'yyyy-mm-dd';

Session altered.

SQL> select value from v$parameter where name = 'nls_date_format';

VALUE
--------------------------------------------------------------------------------
yyyy-mm-dd

1 row selected.


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks

A reader, December 14, 2017 - 4:49 pm UTC


Connor McDonald
December 15, 2017 - 6:38 am UTC

glad we could help

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database