Oracle日期運算問題

不管是資料庫操作或是Shell script撰寫，日期的運算加減是常會遇到的一個問題，Oracle提供了一些常用的運算函數來操作這些日期的運算問題。

1. SYSDATE + or - 天數

EX:
SQL> SELECT SYSDATE FROM dual;
SYSDATE
----------
2011/08/15

2. 日期加數值
EX:
SQL> SELECT SYSDATE+10 FROM dual;
SYSDATE+10
----------
2011/08/25

3. 日期減數值
EX:
SQL> SELECT SYSDATE-15 FROM dual;
SYSDATE-15
----------
2011/07/31

4. 日期相減
EX:
SQL> SELECT SYSDATE- TO_DATE('2011/08/14') FROM dual;
SYSDATE-TO_DATE('2011/08/14')
-----------------------------
1.51289352

SQL> SELECT TRUNC(SYSDATE- TO_DATE('2011/08/14')) FROM dual;
TRUNC(SYSDATE-TO_DATE('2011/08/14'))
------------------------------------
1

5. 日期相減獲得小時差距
EX:
SQL> SELECT TRUNC((SYSDATE - TO_DATE('2011/08/14'))*24) FROM dual;
TRUNC((SYSDATE-TO_DATE('2011/08/14'))*24)
-----------------------------------------
36

SQL> SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') FROM dual;
TO_CHAR(SYSDATE,'YY
-------------------
2011/08/15 12:21:52

6. 日期相減獲得分鐘差距
EX:
SQL> SELECT TRUNC((SYSDATE - TO_DATE('2011/08/14'))*24*60) FROM dual;
TRUNC((SYSDATE-TO_DATE('2011/08/14'))*24*60)
--------------------------------------------
2182

7. 日期相減獲得秒數差距
EX:
SQL> SELECT TRUNC((SYSDATE - TO_DATE('2011/08/14'))*24*60*60) FROM dual;
TRUNC((SYSDATE-TO_DATE('2011/08/14'))*24*60*60)
-----------------------------------------------
130993

8. 日期加 N 小時
EX:
SQL> SELECT TO_CHAR(SYSDATE+(1/24), 'YYYY/MM/DD HH24:MI:SS') FROM dual;
TO_CHAR(SYSDATE+(1/
-------------------
2011/08/15 13:24:37

9. 日期加 N 分鐘
EX:
SQL> SELECT TO_CHAR(SYSDATE+(1/1440), 'YYYY/MM/DD HH24:MI:SS') FROM dual;
TO_CHAR(SYSDATE+(1/
-------------------
2011/08/15 12:26:11

10. 日期加 N 秒數
EX:
SQL> SELECT TO_CHAR(SYSDATE+(1/86400), 'YYYY/MM/DD HH24:MI:SS') FROM dual;
TO_CHAR(SYSDATE+(1/
-------------------
2011/08/15 12:25:33

11. ADD_MONTHS(d, n)
從時間點 d 加上 n 小時
EX:
SQL> SELECT SYSDATE, ADD_MONTHS(SYSDATE, 3) FROM dual;
SYSDATE ADD_MONTHS
---------- ----------
2011/08/15 2011/11/15

12. LAST_DAY(d)
從時間點 d 起，當月的最後一天
EX:
SQL> SELECT SYSDATE, LAST_DAY(SYSDATE) 月底 FROM dual;
SYSDATE 月底
---------- ----------
2011/08/15 2011/08/31

13. NEXT_DAY(d, char)
從時間點 d 開始，下星期幾的日期
char: SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY

EX:
SQL> SELECT SYSDATE, NEXT_DAY(SYSDATE, 'MONDAY') "下星期一" FROM dual;
SYSDATE 下星期一
---------- ----------
2011/08/15 2011/08/22

SQL> SELECT SYSDATE, NEXT_DAY(SYSDATE, 'MONDAY')+1 FROM dual;
SYSDATE NEXT_DAY(S
---------- ----------
2011/08/15 2011/08/23

14. MONTHS_BETWEEN(d1, d2)
計算兩日期之間的相隔月數

EX:
SQL> SELECT TRUNC(MONTHS_BETWEEN('2011/08/31','2011/07/01')) FROM dual;
TRUNC(MONTHS_BETWEEN('2011/08/31','2011/07/01'))
------------------------------------------------
1

以15號為四捨五入
SQL> SELECT ROUND(MONTHS_BETWEEN('2011/08/31','2011/07/01')) FROM dual;
ROUND(MONTHS_BETWEEN('2011/08/31','2011/07/01'))
------------------------------------------------
2

15. NEW_TIME(d, z1, z2)
轉換新時區

EX:
SQL> SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') "遠東地區" ,
2 TO_CHAR(NEW_TIME(SYSDATE,'EST','GMT'),'YYYY/MM/DD HH24:MI:SS') "格林威治"
3 FROM dual;
遠東地區 格林威治
------------------- -------------------
2011/08/15 13:57:47 2011/08/15 18:57:47

16. ROUND(d[, fmt])
對日期作四捨五入的運算
月份以每月15號為基準
年份以六月為基準

EX:
SQL> SELECT SYSDATE,ROUND(SYSDATE,'MONTH') FROM dual;
SYSDATE ROUND(SYSD
---------- ----------
2011/08/15 2011/08/01

SQL> SELECT SYSDATE,ROUND(SYSDATE,'YEAR') FROM dual;
SYSDATE ROUND(SYSD
---------- ----------
2011/08/15 2012/01/01

17. TRUNC(d[, fmt])
對日期作無條件捨去的運算

EX:
SQL> SELECT SYSDATE, TRUNC(SYSDATE,'YEAR') FROM dual;
SYSDATE TRUNC(SYSD
---------- ----------
2011/08/15 2011/01/01

SQL> SELECT SYSDATE, TRUNC(SYSDATE,'MONTH') FROM dual;
SYSDATE TRUNC(SYSD
---------- ----------
2011/08/15 2011/08/01