Oracle Date型別


在本教學中將學習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_dateend_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資料。