Oracle Date类型

精华

小牛编辑

155浏览

2023-03-14

在本教程中将学习Oracle DATE数据类型以及如何有效地处理日期和时间值。

Oracle DATE数据类型简介

DATE数据类型允许以一秒的精度存储包括日期和时间的时间点值。
DATE数据类型存储年份(包括世纪)，月份，日期，小时数，分钟数和秒数。它的范围从公元前4712年1月1日到公元9999年12月31日(共同时代)。默认情况下，如果未明确使用BCE，则Oracle使用CE日期条目。

Oracle数据库有其自己的专用格式来存储日期数据。它使用7个字节的固定长度的字段，每个字段对应于世纪，年，月，日，时，分和秒来存储日期数据。

1. Oracle日期格式

输入和输出的标准日期格式是DD-MON-YY，例如由NLS_DATE_FORMAT参数的值表示为：
01-JAN-17。

以下语句显示了NLS_DATE_FORMAT参数的当前值：

SELECT
  value
FROM
  V$NLS_PARAMETERS
WHERE
  parameter = 'NLS_DATE_FORMAT';

在Oracle数据库系统中，NLS_DATE_FORMAT的值是：

DD-MON-RR

以下语句通过使用SYSDATE函数以标准日期格式返回当前日期。

SELECT
  sysdate
FROM
  dual;

执行上面查询语句，得到以下结果 -

假设想要将标准日期格式更改为YYYY-MM-DD，那么可以使用ALTER SESSION语句来更改NLS_DATE_FORMAT参数的值，如下所示：

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';

要验证更改，可以执行显示当前系统日期的语句：

2. 使用TO_CHAR()函数格式化日期

TO_CHAR()函数采用DATE值作为参数，根据指定的格式对其进行格式化，并返回一个日期字符串。

例如，要以特定的格式显示当前的系统日期，请按如下所示使用TO_CHAR()函数：

SELECT
  TO_CHAR( SYSDATE, 'MM/DD/YYYY' )
FROM
  dual;

执行上面查询语句，得到以下结果 -

TO_CHAR()函数用于显示月份名称的语言由NLS_DATE_LANGUAGE参数控制：

SELECT
  value
FROM
  V$NLS_PARAMETERS
WHERE
  parameter = 'NLS_DATE_LANGUAGE';

执行上面查询语句，得到以下结果 -

如果要将当前语言更改为另一个语言(例如FRENCH)，请使用ALTER SESSION语句：

ALTER SESSION SET NLS_DATE_LANGUAGE = 'FRENCH';
-- alter session set nls_language='SIMPLIFIED CHINESE';

现在，可以再次执行TO_CHAR()函数来查看效果：

正如上面结果所看到的，这个月的名字已经从英文变成了法文。

3. 将字符串转换为日期

由于Oracle使用内部格式来存储DATE数据，因此在将日期值存储到日期列之前，通常必须将字符串转换为日期值。
要转换不是标准格式的日期值，可以使用带有格式字符串的TO_DATE()函数。

以下示例使用TO_DATE()函数将字符串“August 01，2017”转换为相应的日期：

-- alter session set nls_language='SIMPLIFIED CHINESE';
SELECT
  TO_DATE('2018-10-21', 'YYYY-MM-DD' )
FROM
  dual;

执行上面查询语句，得到以下结果 -

2018-10-21

4. 日期文字

除了使用TO_DATE()函数之外，还可以使用以下语法将日期值指定为字符串文字：

DATE 'YYYY-MM-DD'

请注意，要使用日期作为文字，必须使用公历。以下示例显示2017年8月1日的ANSI日期字面值：

DATE '2017-08-01'

ANSI日期文字没有时间部分，必须采用确切格式('YYYY-MM-DD')。如果要将时间数据包含在日期值中，则必须使用上面介绍的TO_DATE()函数。

Oracle DATE数据类型示例

以下语句创建一个名为my_events的表：

CREATE TABLE my_events (
    event_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    event_name VARCHAR2 ( 255 ) NOT NULL,
    location VARCHAR2 ( 255 ) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    PRIMARY KEY ( event_id ) 
);

在这个表中，我们有两列DATE数据类型，分别是：start_date和end_date 。

要将新行插入到my_events表中，请使用以下语句：

INSERT INTO my_events
            (event_name,
             location,
             start_date,
             end_date)
VALUES     ( 'TechEd Europe',
        'Barcelona, Spain',
            DATE '2017-11-14',
            DATE '2017-11-16' );

在这个例子中，使用INSERT语句插入了两个日期文字。可以使用TO_DATE()函数在插入之前将字符串转换为日期，如下例所示：

INSERT INTO my_events
            (event_name,
             location,
             start_date,
             end_date)
VALUES     ( 'Oracle OpenWorld',
        'San Francisco, CA, USA',
            TO_DATE( '2017-10-01', 'YYYY-MM-DD' ),
            TO_DATE( '2017-10-05', 'YYYY-MM-DD'));

我们再插入一行进行测试。

INSERT INTO my_events
                (event_name,
                 location,
                 start_date,
                 end_date)
    VALUES     ( 'TechEd US',
            'Las Vegas, NV, USA',
                DATE '2017-09-25',
                DATE '2017-09-29' );

以下查询返回my_events表中的所有行：

SELECT
  *
FROM
  my_events;

执行上面查询语句，得到以下结果 -

当然，也可以使用TO_CHAR()函数来格式化事件的日期：

SELECT
  event_name,
  location,
  TO_CHAR(start_data, 'FMmonth DD, YYYY') start_date,
  TO_CHAR(end_data, 'FMmonth DD, YYYY') start_date,
FROM
  my_events;

执行上面查询语句，得到以下结果 -

在本教程中，您已经了解了Oracle Date数据类型以及如何有效处理Date数据。