6-1-3 日期的處理方式

Oracle 資料庫會以內部數字格式來儲存日期, 代表世紀、年、月、日、小時、分鐘以及秒。預設的日期顯示格式為 DD-MON-RR。

• 能藉由只指定年份的末兩位數,將21世紀的日期,儲存在 20 世紀
• 能以同樣的方式,將20世紀的日期,儲存在21世 紀

SELECT last_name, hire_date FROM employees WHERE hire_date< '01-FEB-88';

範例中,HIRE_date 資料欄的輸出是以預設的格式 DD-MON-RR 來顯示。但卻不 是以此格式儲存在資料庫中。日期與時間的所有元件都會被儲存。所以,雖然像 17-JUN-87 這 樣的HIRE_date 會顯示為日、月、年,但還有與此日期相關聯的時間與世紀資訊。因此,完 整的日期會是 June 17, 1987, 5:10:43 p.m。

日期是以下列格式儲存於 Oracle 內部:
CENTURY    YEAR    MONTH    DAY    HOUR    MINUTE     SECOND
19                   88        07             16       14           12               53
世紀與 Y2K

當一筆含有日期資料欄的記錄插入表格時,就會從 SYSDATE 函數中取得世紀資訊。不過,當日期資料欄顯示於畫面時,並不會顯示世紀元件 (預設)。DATE 資料類型會將年份資訊以四位數字儲存於內部:兩個位數是世紀,而另外兩個位數則為年份。例如,Oracle 資料庫會將年份儲存為 1987 或 2004,而不會儲存為 87 或 04。

SYSDATE 函數

SYSDATE 是一種日期函數,可傳回目前資料庫伺服器的日期與時間。就像您可以使用其他資料欄名稱一樣,您也可以使用 SYSDATE。舉例來說,您可以從一個表格中選擇 SYSDATE 來顯示目前的日期。通常會從一個稱為 DUAL 的虛擬表格中選取 SYSDATE。
範例使用 DUAL 表格來顯示目前的日期。

SELECT SYSDATE FROM DUAL;

日期運算

• 在結果日期值加上或減去一個日期的數字。
• 將兩個日期相減,以找出這兩個日期之間的天數。
• 將小時的數字除以 24,來將小時新增到一個日期中。

作業	結果	說明
日期 + 數字	日期	將一定數目的天數加到日期
日期 – 數字	日期	將一定數目的天數從日期減去
日期 – 日期	天數	將一個日期減掉另一個日期
日期 + 數字/24	日期	將一定數目的小時加到日期上

SELECT last_name,(SYSDATE-hire_date)/7 AS WEEKS FROM employees WHERE  department_id = 90;

範例顯示部門 90 所有員工的姓氏與自聘僱起所經過的週數。其中,將目前日期(SYSDATE) 減去員工聘僱日期再除以 7,以計算一名員工已聘僱的週數。

注意:SYSDATE 是一種用來傳回目前日期與時間的 SQL 函數。您的結果可能會與範例所有不同。如果用較早的日期來減掉較近的日期,則差額會是負數。

日期函數

日期函數會在 Oracle 的日期上作業。所有日期函數都會傳回一個 DATE 資料類型的值,但 MONTHS_BETWEEN 除外,因為此函數會傳回一個數值。

• MONTHS_BETWEEN(date1, date2):尋找 date1 與 date2 之間的月數。結果可能是 正數也可能是負數。若 date1 晚於 date2,則結果會是正數;反之,若 date1 早於 date2,則結果會是負數。結果的非整數部份,代表不滿一個月的部份。
• ADD_MONTHS(date, n):將日曆月份的 n 數字加到 date 上。n 值必須是整數,且可 為負數。
• NEXT_DAY(date, ‘char’):尋找日期 date 後下一個指定的星期幾 (‘char’)。 char 的值可以是代表一天的數字,也可以是一個字元字串。
• LAST_DAY(date):尋找包含 date 的月份的最後一天。
• ROUND(date[,‘fmt’]):傳回四捨五入到格式模型 fmt 所指定的單位之 date。若忽略格式模型 fmt,則 date 會被四捨五入到最近的一天。
• TRUNC(date[, ‘fmt’]):傳回 date 以及經格式模型 fmt 所指定的單位截斷的一日部份時間。若忽略格式模型fmt,則date 會被截斷到最近的一天。

使用日期函數

函數	結果
MONTHS_BETWEEN ('01-SEP-95','11-JAN-94')	19.6774194
ADD_MONTHS ('11-JAN-94',6)	'11-JUL-94'
NEXT_DAY ('01-SEP-95','FRIDAY')	'08-SEP-95'
LAST_DAY ('01-FEB-95')	'28-FEB-95'

舉例來說,可顯示所有聘僱時間少於三十六個月的員工之員工編號、聘僱日期、已聘僱月數、六個月檢討日期、聘僱日期後的第一個星期五以及最後聘僱月日期等資料。

SELECT employee_id, hire_date,       MONTHS_BETWEEN (SYSDATE, hire_date) TENURE,       ADD_MONTHS (hire_date, 6) REVIEW,       NEXT_DAY (hire_date, 'FRIDAY'),  LAST_DAY(hire_date) FROM employees WHERE MONTHS_BETWEEN (SYSDATE, hire_date) < 36;

ROUND 函數與 TRUNC 函數可用於數字值與日期值。用在日期上時,這些函數可以四捨五入或截斷成指定的格式模型。所以您可以將日期四捨五入到最近的年份或月份。假設SYSDATE= '25-JUL-03':

函數	結果
ROUND(SYSDATE,'MONTH')	01/08/2003
ROUND(SYSDATE,'YEAR')	01/01/2004
TRUNC(SYSDATE,'MONTH')	01/07/2003
TRUNC(SYSDATE,'YEAR')	01/01/2003

範例
比較一下所有從 1997 年開始上班的員工之聘僱日期。使用 ROUND 函數與 TRUNC 函數來顯示員工編號、聘僱日期以及開始日期。

SELECT employee_id, hire_date,       ROUND(hire_date, 'MONTH'),       TRUNC(hire_date, 'MONTH') FROM employees WHERE hire_dateLIKE '%97';