常用格式元素速查表
| 類別 | 格式 | 說明 | 範例 |
|---|
| 日 | DD | 兩位數字 | 12 |
| DY | 星期縮寫 | FRI |
| DAY | 星期全名 | FRIDAY |
| DDSPTH | 序數拼寫 | TWELFTH |
| 月 | MM | 兩位數字 | 03 |
| MON | 月份縮寫 | MAR |
| MONTH | 月份全名 | MARCH |
| 年 | YY | 兩位年份 | 98 |
| YYYY | 四位年份 | 1998 |
| RRRR | 智能兩位年份 | 99→1999, 01→2001 |
| 時制 | HH24 / HH12 | 24/12 小時制 | 0-23 / 1-12 |
21 個實戰範例精選
1. 日期字串轉換基礎
TO_DATE('2002-08-26','YYYY-MM-DD')
TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')
2. 數字轉中文大寫 (JSP)
SELECT TO_CHAR(TO_DATE(222,'J'),'JSP') FROM DUAL;
-- 顯示: TWO HUNDRED TWENTY-TWO
3. 判斷星期幾 (可指定語言)
SELECT TO_CHAR(TO_DATE('2002-08-26','YYYY-MM-DD'),'DAY') FROM DUAL; -- 星期一
SELECT TO_CHAR(TO_DATE('2002-08-26','YYYY-MM-DD'),'DAY','NLS_DATE_LANGUAGE=AMERICAN') FROM DUAL; -- MONDAY
-- Session 級設定: ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';
4. 兩日期相差天數
SELECT FLOOR(SYSDATE - TO_DATE('20020405','YYYYMMDD')) FROM DUAL;
5. 處理 NULL 日期
SELECT id, active_date FROM table1
UNION
SELECT 1, TO_DATE(NULL) FROM DUAL; -- 必須用 TO_DATE(NULL)
6. BETWEEN 日期範圍陷阱
-- 以下範圍不含 12/31 12:00 後及 12/1 12:00 前
a_date BETWEEN TO_DATE('20011201','YYYYMMDD') AND TO_DATE('20011231','YYYYMMDD')
-- 精確建議改用 TO_CHAR 或明確加上時間
7. NLS 日期語言衝突解決
-- 三種方式任選一
ALTER SYSTEM SET NLS_DATE_LANGUAGE = AMERICAN;
ALTER SESSION SET NLS_DATE_LANGUAGE = AMERICAN;
TO_DATE('2002-08-26','YYYY-MM-DD','NLS_DATE_LANGUAGE=AMERICAN')
8. 計算工作天數 (排除週末)
SELECT COUNT(*) FROM (
SELECT ROWNUM-1 rnum FROM all_objects
WHERE ROWNUM <= TO_DATE('2002-02-28','YYYY-MM-DD') - TO_DATE('2002-02-01','YYYY-MM-DD') + 1
) WHERE TO_CHAR(TO_DATE('2002-02-01','YYYY-MM-DD')+rnum-1, 'D') NOT IN ('1','7');
9. MONTHS_BETWEEN 細節
SELECT MONTHS_BETWEEN(TO_DATE('01-31-1999','MM-DD-YYYY'), TO_DATE('12-31-1998','MM-DD-YYYY')) FROM DUAL; -- 1
SELECT MONTHS_BETWEEN(TO_DATE('02-01-1999','MM-DD-YYYY'), TO_DATE('12-31-1998','MM-DD-YYYY')) FROM DUAL; -- 1.032...
10. NEXT_DAY 用法
NEXT_DAY(SYSDATE, 'FRIDAY') -- 下一個星期五
NEXT_DAY(SYSDATE, 6) -- 6=星期五 (1=週日)
11. 解決 SYSDATE 在查詢中不變的問題
CREATE OR REPLACE FUNCTION sys_date RETURN DATE IS BEGIN RETURN SYSDATE; END;
SELECT TO_CHAR(sys_date, 'HH24:MI:SS') FROM all_objects; -- 每列時間不同
12. 擷取時/分/秒/毫秒
SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 02:38:40') FROM DUAL;
SELECT TO_CHAR(SYSDATE,'HH24') FROM DUAL; -- 24小時制
13. 精確年月日差異計算
SELECT older_date, newer_date,
TRUNC(MONTHS_BETWEEN(newer_date, older_date)/12) years,
MOD(TRUNC(MONTHS_BETWEEN(newer_date, older_date)),12) months,
ABS(TRUNC(newer_date - ADD_MONTHS(older_date, years*12+months))) days
FROM (SELECT HIREDATE older_date, ADD_MONTHS(HIREDATE, ROWNUM)+ROWNUM newer_date FROM EMP);
14. 處理月份天數不固定
SELECT TO_CHAR(ADD_MONTHS(LAST_DAY(SYSDATE)+1, -2), 'YYYYMMDD'), LAST_DAY(SYSDATE) FROM DUAL;
15. 判斷閏年 & 當年總天數
-- 當年總天數
SELECT ADD_MONTHS(TRUNC(SYSDATE,'YEAR'),12) - TRUNC(SYSDATE,'YEAR') FROM DUAL;
-- 閏年判斷: 2月最後一天是 29 號即為閏年
TO_CHAR(LAST_DAY(TO_DATE('02'||:year,'MMYYYY')),'DD')
16. YYYY vs RRRR 差異
-- YYYY: 99→0099, 01→0001
-- RRRR: 99→1999, 01→2001 (跨世紀智能轉換)
17. 時區轉換
SELECT TO_CHAR(NEW_TIME(SYSDATE,'GMT','EST'),'DD/MM/YYYY HH:MI:SS'), SYSDATE FROM DUAL;
18. 固定間隔截取 (如每 5 分鐘)
SELECT TO_DATE(FLOOR(TO_CHAR(SYSDATE,'SSSSS')/300)*300,'SSSSS'), TO_CHAR(SYSDATE,'SSSSS') FROM DUAL;
-- SSSSS = 當日累積秒數
19. 當年第幾天
SELECT TO_CHAR(SYSDATE,'DDD'), SYSDATE FROM DUAL; -- 310 (11/6)
20. 完整時分秒毫秒拆解
SELECT days, a,
TRUNC(a*24) hours,
TRUNC(a*24*60 - 60*TRUNC(a*24)) minutes,
TRUNC(a*24*60*60 - 60*TRUNC(a*24*60)) seconds,
TRUNC(a*24*60*60*100 - 100*TRUNC(a*24*60*60)) mseconds
FROM (SELECT TRUNC(SYSDATE) days, SYSDATE-TRUNC(SYSDATE) a FROM DUAL);
21. 自訂排序技巧
ORDER BY DECODE(mode,'FIFO',1,-1)*TO_CHAR(rq,'YYYYMMDDHH24MISS');
