NLS_DATE_FORMAT参数详解

最新推荐文章于 2024-03-28 15:49:43 发布

原创最新推荐文章于 2024-03-28 15:49:43 发布 · 2.7k 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#Oracle #NLS_DATE_FORMAT

Oracle技术专栏收录该内容

16 篇文章

订阅专栏

本文介绍了在Oracle数据库中设置日期格式的多种方式，并详细探讨了不同设置之间的优先级关系。包括初始化参数、环境变量、会话级参数及函数级别的设置。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、问题的引出

NLS_DATE_FORMAT参数用于设置日期显示格式，设置的方式有多种，不同的方式也会带来不同的结果。

我们知道，参数的设置是有优先级的，日期格式的参数设置也不例外。优先级如下（低到高）：

● 初始化参数 < 系统环境变量 < 会话级（session）< 函数

我们先来查看系统默认的日期显示方式：

SQL> select sysdate from dual;

SYSDATE

--------------

31-DEC-12

下面我们通过几种设置方式来对NLS_DATE_FORMAT参数进行修改。

二、初始化参数设置

查看该参数的值：

SQL> show parameter nls_date_format

NAME TYPE VALUE

------------------------------------ --------------------------------------------------------------

nls_date_format string

可以看到，未设置时，value为空，即使用系统默认设置，也可以通过以下方式查看：

SQL> select name,value,isspecified fromv$spparameter where name='nls_date_format';

NAME VALUE ISSPECIFIED

------------------------------ --------------------------------------------------

nls_date_format FALSE

对于初始化参数的设置其实也有几种方式。

2.1、修改init$ORACLE_SID.ora文件

● 方法一：在初始化参数文件中追加：

[oracle@localhostdbs]$ vi init$ORACLE_SID.ora

NLS_DATE_FORMAT='yyyy-Month-dd'

因为初始化参数文件只有在启动数据库的时候才被载入，所以我们要重启数据库，如果存在spfile.ora文件，则要在关闭数据库的时候重建spfile文件。

关闭数据库：

SQL> shutdown immediate

Database closed.

Databasedismounted.

ORACLE instanceshut down.

创建（或重建）spfile文件：

SQL> create spfile from pfile;

File created.

启动数据库：

SQL> startup

ORACLE instancestarted.

Total SystemGlobal Area 524288000 bytes

Fixed Size 2022080 bytes

VariableSize 180356416 bytes

DatabaseBuffers 339738624 bytes

RedoBuffers 2170880 bytes

Databasemounted.

Database opened.

重新查询参数设置情况：

SQL> show parameter nls_date_for

NAME TYPE VALUE

------------------------------------ --------------------------------------------------------------

nls_date_format string yyyy-Month-dd

SQL> select name,value,isspecified fromv$spparameter where name='nls_date_format';

NAME VALUE ISSPECIFIED

-------------------- -------------------- --------------------

nls_date_format yyyy-Month-dd TRUE

查询当前系统时间，发现时间显示格式已经生效了：

SQL> select sysdate from dual;

SYSDATE

-----------------

2012-December -31

当然，如果存在spfile文件，也可使用ALTERSYSTEM语句动态修改，但修改后同样需要重启数据库。

● 方法二：动态修改spfile文件

SQL> alter system setNLS_DATE_FORMAT='yyyy:mm:dd' scope=spfile;

System altered.

关闭数据库：

SQL> shutdown immediate

Database closed.

Databasedismounted.

ORACLE instanceshut down.

这里可以顺便根据spfile内容来创建pfile文件：

SQL> create pfile from spfile;

File created.

启动数据库：

SQL> startup

ORACLE instancestarted.

Total SystemGlobal Area 524288000 bytes

Fixed Size 2022080 bytes

VariableSize 180356416 bytes

DatabaseBuffers 339738624 bytes

RedoBuffers 2170880 bytes

Database mounted.

Database opened.

测试，查询日期：

SQL> select sysdate from dual;

SYSDATE

--------------

2012:12:31

2.2、修改环境变量

修改环境变量不需要重启数据库，只需要使环境变量生效即可。

我们现在只在环境变量文件追加以下内容，看看结果怎样：

[oracle@redhat4~]$ vi .bash_profile

export NLS_LANG=AMERICAN_AMERICA.zhs16gbk

[oracle@redhat4~]$ source .bash_profile

我们可以发现，现在系统的时间显示格式又恢复到了默认，这是为什么呢？

因为NLS_LANG参数同样会影响到NLS_DATE_FORMAT参数，只要存在NLS_LANG参数，Oracle就会使用环境变量的值（即使NLS_DATE_FORMAT），根据上面我们提到的优先级，所以初始化参数文件设置的值会被忽略，但由于环境变量中NLS_DATE_FORMAT并没有设置，所以Oracle还是使用了默认的格式。

SQL> select sysdate from dual;

SYSDATE

------------

31-DEC-12

下面我们把NLS_DATE_FORMAT也设置上去，追加：

[oracle@redhat4~]$ vi .bash_profile

exportNLS_DATE_FORMAT='yyyy-mm-dd HH24:MI:SS'

[oracle@redhat4~]$ source .bash_profile

查询系统时间：

SQL> select sysdate from dual;

SYSDATE

-------------------------