前言
同名函數 CONVERT 在 Oracle 與 SQL Server 功能截然不同，易導致移植錯誤。Oracle CONVERT 僅用於字元集轉換，不支援日期格式化；SQL Server CONVERT 主要用於日期/數值格式化。本文詳細對照兩者語法與用法。
一、SQL Server CONVERT - 日期格式化核心
語法
CONVERT( data_type(length), expression [, style ] )
style 為格式代碼，決定輸出字串格式。- 常用於
datetime → varchar 轉換。
常用 Style 代碼表 (GETDATE() 為 2006-05-16 10:57:46)
| Style | 輸出範例 | 說明 |
|---|
| 0 / 100 | May 16 2006 10:57AM | 預設 mon dd yyyy hh:miAM/PM |
| 1 / 101 | 05/16/06 / 05/16/2006 | 美式 mm/dd/yy / mm/dd/yyyy |
| 2 / 102 | 06.05.16 / 2006.05.16 | yy.mm.dd / yyyy.mm.dd |
| 3 / 103 | 16/05/06 / 16/05/2006 | 英式 dd/mm/yy / dd/mm/yyyy |
| 4 / 104 | 16.05.06 / 16.05.2006 | dd.mm.yy / dd.mm.yyyy |
| 5 / 105 | 16-05-06 / 16-05-2006 | dd-mm-yy / dd-mm-yyyy |
| 6 / 106 | 16 05 06 / 16 05 2006 | dd mon yy / dd mon yyyy |
| 7 / 107 | May 16, 06 / May 16, 2006 | mon dd, yy / mon dd, yyyy |
| 8 / 108 | 10:57:46 | hh:mi:ss |
| 9 / 109 | May 16 2006 10:57:46:827AM | mon dd yyyy hh:mi:ss:mmmAM/PM |
| 10 / 110 | 05-16-06 / 05-16-2006 | mm-dd-yy / mm-dd-yyyy |
| 11 / 111 | 06/05/16 / 2006/05/16 | yy/mm/dd / yyyy/mm/dd |
| 12 / 112 | 060516 / 20060516 | yymmdd / yyyymmdd |
| 13 / 113 | 16 05 2006 10:57:46:937 | dd mon yyyy hh:mi:ss:mmm (24h) |
| 14 / 114 | 10:57:46:967 | hh:mi:ss:mmm (24h) |
| 20 / 120 | 2006-05-16 10:57:47 | yyyy-mm-dd hh:mi:ss (24h) |
| 21 / 121 | 2006-05-16 10:57:47.157 | yyyy-mm-dd hh:mi:ss.mmm (24h) |
| 22 | 05/16/06 10:57:47 AM | mm/dd/yy hh:mi:ss AM/PM |
| 23 | 2006-05-16 | yyyy-mm-dd |
| 24 | 10:57:47 | hh:mi:ss |
| 25 | 2006-05-16 10:57:47.250 | yyyy-mm-dd hh:mi:ss.mmm |
| 126 | 2006-05-16T10:57:49.827 | ISO 8601 (yyyy-mm-ddThh:mi:ss.mmm) |
| 131 | 18/04/1427 10:57:49:920AM | 伊斯蘭曆 dd/mm/yyyy hh:mi:ss:mmmAM |
截取年/月/日
SELECT CONVERT(varchar(4), GETDATE(), 112); -- 2006 (年)
SELECT CONVERT(varchar(2), GETDATE(), 101); -- 05 (月)
SELECT CONVERT(varchar(2), GETDATE(), 102); -- 16 (日)
二、Oracle CONVERT - 字元集轉換
語法
CONVERT( char, dest_char_set, source_char_set )
- 將字串從
source_char_set 轉換為 dest_char_set。
- 完全不支援日期格式化。
範例
SELECT CONVERT('strutz', 'WE8HP', 'F7DEC') FROM dual;
-- 將字串從 F7DEC (DEC MCS) 轉為 WE8HP (HP West European)
常用字元集代碼
| 代碼 | 名稱 | 適用區域 |
|---|
| US7ASCII | ASCII 7-bit | 英語 |
| WE8ISO8859P1 | ISO 8859-1 | 西歐 |
| WE8MSWIN1252 | Windows CP1252 | 西歐 Windows |
| ZHT16BIG5 | Big5 | 繁體中文 |
| ZHT16MSWIN950 | CP950 | 繁體中文 Windows |
| ZHS16GBK | GBK | 簡體中文 |
| AL32UTF8 | UTF-8 | 通用 Unicode |
三、Oracle 日期格式化正確函數：TO_CHAR
Oracle 日期 → 字串格式化使用 TO_CHAR：
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM dual; -- 2024-01-15 14:30:45
SELECT TO_CHAR(SYSDATE, 'YYYYMMDD') FROM dual; -- 20240115
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM dual; -- 2024-01-15
SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS') FROM dual; -- 14:30:45
SELECT TO_CHAR(SYSDATE, 'Day, Month DD, YYYY') FROM dual; -- Monday, January 15, 2024
常用格式元素
| 元素 | 意義 | 範例 |
|---|
| YYYY / RRRR | 4 位年份 | 2024 |
| MM | 月份 (01-12) | 01 |
| MON / MONTH | 月份縮寫/全名 | JAN / JANUARY |
| DD | 日期 (01-31) | 15 |
| HH / HH12 | 12 小時制 | 02 |
| HH24 | 24 小時制 | 14 |
| MI | 分鐘 (00-59) | 30 |
| SS | 秒 (00-59) | 45 |
| FF / FF3 | 微秒 / 毫秒 | .123456 / .123 |
| AM / PM | 上午/下午標記 | PM |
四、移植對照快速參考
| 需求 | SQL Server | Oracle |
|---|
| 日期 → 字串 (yyyy-mm-dd) | CONVERT(varchar(10), dt, 23) | TO_CHAR(dt, 'YYYY-MM-DD') |
| 日期 → 字串 (yyyy-mm-dd hh:mi:ss) | CONVERT(varchar(19), dt, 20) | TO_CHAR(dt, 'YYYY-MM-DD HH24:MI:SS') |
| 字串 → 日期 | CONVERT(datetime, '2024-01-15', 23) | TO_DATE('2024-01-15', 'YYYY-MM-DD') |
| 字元集轉換 | COLLATE / CAST(... AS VARCHAR(...) COLLATE ...) | CONVERT(str, 'AL32UTF8', 'ZHT16BIG5') |
| 數值格式化 | CONVERT(varchar, 1234.5, 1) (加千分位) | TO_CHAR(1234.5, '9,999.9') |
五、最佳實務建議
- 切勿混淆：看到
CONVERT 立即確認是 Oracle 還是 SQL Server 語境。
- 統一格式化邏輯：應用層使用標準庫 (Java
DateTimeFormatter, C# ToString("yyyy-MM-dd")) 格式化，避免資料庫層面耦合特定方言。
- 移植專案建立對照表：列出所有使用
CONVERT、TO_CHAR、DATE_FORMAT (MySQL) 的位置，逐一轉換驗證。
- 字元集轉換慎用：資料庫層面字元集轉換易導致資料損壞，建議在應用層或 ETL 階段以 Unicode (UTF-8) 統一處理。
總結：SQL Server CONVERT = 格式化瑞士刀；Oracle CONVERT = 字元集轉換專用工具；Oracle 格式化請用 TO_CHAR/TO_DATE。移植時務必區分語境，避免「同名不同命」陷阱。
