[20170918]NLS_DATE_FORMAT设置.txt
--//昨天看了链接www.cnblogs.com/kerrycode/p/7518890.html,里面提到
注意：环境变量NLS_DATE_FORMAT必须和NLS_LANG一起设置，否则不会生效(这个才是总结这篇文章的初衷，以前一直没有注意这个问题
)。可以直接在会话窗口使用export或.bash_profile配置文件(全局应用)设置。
--//我自己也测试看看.
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
$ env | grep -i nls
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
NLS_TIMESTAMP_TZ_FORMAT=YYYY-MM-DD HH24:MI:SS.FF TZH:TZM
NLS_TIMESTAMP_FORMAT=YYYY-MM-DD HH24:MI:SS.FF
NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS
--//取消设置NLS_DATE_FORMAT,NLS_LANG后重新登录.
$ export NLS_LANG=
$ export NLS_DATE_FORMAT=
$ env | grep -i nls
NLS_LANG=
NLS_TIMESTAMP_TZ_FORMAT=YYYY-MM-DD HH24:MI:SS.FF TZH:TZM
NLS_TIMESTAMP_FORMAT=YYYY-MM-DD HH24:MI:SS.FF
NLS_DATE_FORMAT=
SCOTT@book> show parameter nls_
NAME TYPE VALUE
----------------------- ------- -------
nls_calendar string
nls_comp string BINARY
nls_currency string
nls_date_format string
nls_date_language string
nls_dual_currency string
nls_iso_currency string
nls_language string AMERICAN
nls_length_semantics string BYTE
nls_nchar_conv_excp string FALSE
nls_numeric_characters string
nls_sort string
nls_territory string AMERICA
nls_time_format string
nls_time_tz_format string
nls_timestamp_format string
nls_timestamp_tz_format string
SCOTT@book> select sysdate from dual ;
SYSDATE
---------
19-SEP-17
$ export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
$ env | grep -i nls
NLS_LANG=
NLS_TIMESTAMP_TZ_FORMAT=YYYY-MM-DD HH24:MI:SS.FF TZH:TZM
NLS_TIMESTAMP_FORMAT=YYYY-MM-DD HH24:MI:SS.FF
NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS
--//再次重新登录:
SCOTT@book> show parameter nls_
NAME TYPE VALUE
------------------------ ------ ----------
nls_calendar string
nls_comp string BINARY
nls_currency string
nls_date_format string
nls_date_language string
nls_dual_currency string
nls_iso_currency string
nls_language string AMERICAN
nls_length_semantics string BYTE
nls_nchar_conv_excp string FALSE
nls_numeric_characters string
nls_sort string
nls_territory string AMERICA
nls_time_format string
nls_time_tz_format string
nls_timestamp_format string
nls_timestamp_tz_format string
SCOTT@book> select sysdate from dual ;
SYSDATE
---------
19-SEP-17
--//通过修改会话也有效.
SCOTT@book> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
Session altered.
SCOTT@book> select sysdate from dual ;
SYSDATE
-------------------
2017-09-19 09:00:30
$ export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
$ env | grep -i nls
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
NLS_TIMESTAMP_TZ_FORMAT=YYYY-MM-DD HH24:MI:SS.FF TZH:TZM
NLS_TIMESTAMP_FORMAT=YYYY-MM-DD HH24:MI:SS.FF
NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS
SCOTT@book> show parameter nls_
NAME TYPE VALUE
----------------------- ------- ---------------------------------
nls_calendar string GREGORIAN
nls_comp string BINARY
nls_currency string $
nls_date_format string YYYY-MM-DD HH24:MI:SS
nls_date_language string AMERICAN
nls_dual_currency string $
nls_iso_currency string AMERICA
nls_language string AMERICAN
nls_length_semantics string BYTE
nls_nchar_conv_excp string FALSE
nls_numeric_characters string .,
nls_sort string BINARY
nls_territory string AMERICA
nls_time_format string HH.MI.SSXFF AM
nls_time_tz_format string HH.MI.SSXFF AM TZR
nls_timestamp_format string YYYY-MM-DD HH24:MI:SS.FF
nls_timestamp_tz_format string YYYY-MM-DD HH24:MI:SS.FF TZH:TZM
SCOTT@book> select sysdate from dual ;
SYSDATE
-------------------
2017-09-19 08:58:19
$ export NLS_DATE_FORMAT=
$ export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
$ env | grep -i nls
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
NLS_TIMESTAMP_TZ_FORMAT=YYYY-MM-DD HH24:MI:SS.FF TZH:TZM
NLS_TIMESTAMP_FORMAT=YYYY-MM-DD HH24:MI:SS.FF
NLS_DATE_FORMAT=
SCOTT@book> show parameter nls_
NAME TYPE VALUE
----------------------- ------ ---------------------------------
nls_calendar string GREGORIAN
nls_comp string BINARY
nls_currency string $
nls_date_format string DD-MON-RR
nls_date_language string AMERICAN
nls_dual_currency string $
nls_iso_currency string AMERICA
nls_language string AMERICAN
nls_length_semantics string BYTE
nls_nchar_conv_excp string FALSE
nls_numeric_characters string .,
nls_sort string BINARY
nls_territory string AMERICA
nls_time_format string HH.MI.SSXFF AM
nls_time_tz_format string HH.MI.SSXFF AM TZR
nls_timestamp_format string YYYY-MM-DD HH24:MI:SS.FF
nls_timestamp_tz_format string YYYY-MM-DD HH24:MI:SS.FF TZH:TZM
SCOTT@book> select sysdate from dual ;
SYSDATE
------------
19-SEP-17
--//总之定义全局的NLS_DATE_FORMAT要以定义全局的NLS_LANG才有效.oracle有时候真的很奇怪.