[轉]如何使用 CAST 與 CONVERT 格式化日期與時間資料

資料來源：
http://ithelp.ithome.com.tw/question/10008820

SQL Server 的 CAST 與 CONVERT 可以讓我們明確地轉換運算式的資料型別，這次我們就是要討論如何使用這兩個函式來格式化日期與時間資料。


CAST 與 CONVERT 都提供類似的功能讓我們明確地轉換運算式的資料型別，我們就先看看 CONVERT 的語法：

檢視原始檔複製到剪貼簿列印關於

1. CONVERT ( 資料型別 [ (資料長度) ] , 運算式 [ , 日期格式樣式 ] )  

由上面的語法可以看出來 CONVERT 擁有指定「日期格式樣式」的選項，就讓我們透過實際的例子來看看轉換效果：

檢視原始檔複製到剪貼簿列印關於

1. -- 定義變數  
2. DECLARE @myDate DATETIME  
3. -- 指派一個日期給該變數  
4. SET @myDate = '2008/09/09 08:25 AM'  
5.   
6. -- 開始轉換  
7. PRINT CONVERT(CHAR(19), @myDate)  
8. PRINT CONVERT(CHAR(19), @myDate, 0)  
9. PRINT CONVERT(CHAR(8),  @myDate, 1)  
10. PRINT CONVERT(CHAR(8),  @myDate, 2)  
11. PRINT CONVERT(CHAR(8),  @myDate, 3)  
12. PRINT CONVERT(CHAR(8),  @myDate, 4)  
13. PRINT CONVERT(CHAR(8),  @myDate, 5)  
14. PRINT CONVERT(CHAR(9),  @myDate, 6)  
15. PRINT CONVERT(CHAR(10), @myDate, 7)  
16. PRINT CONVERT(CHAR(8),  @myDate, 8)  
17. PRINT CONVERT(CHAR(26), @myDate, 9)  
18. PRINT CONVERT(CHAR(8),  @myDate, 10)  
19. PRINT CONVERT(CHAR(8),  @myDate, 11)  
20. PRINT CONVERT(CHAR(6),  @myDate, 12)  
21. PRINT CONVERT(CHAR(24), @myDate, 13)  
22. PRINT CONVERT(CHAR(12), @myDate, 14)  
23. PRINT CONVERT(CHAR(19), @myDate, 20)  
24. PRINT CONVERT(CHAR(23), @myDate, 21)  
25. PRINT CONVERT(CHAR(20), @myDate, 22)  
26. PRINT CONVERT(CHAR(10), @myDate, 23)  
27. PRINT CONVERT(CHAR(8),  @myDate, 24)  
28. PRINT CONVERT(CHAR(23), @myDate, 25)  
29. PRINT CONVERT(CHAR(19), @myDate, 100)  
30. PRINT CONVERT(CHAR(10), @myDate, 101)  
31. PRINT CONVERT(CHAR(10), @myDate, 102)  
32. PRINT CONVERT(CHAR(10), @myDate, 103)  
33. PRINT CONVERT(CHAR(10), @myDate, 104)  
34. PRINT CONVERT(CHAR(10), @myDate, 105)  
35. PRINT CONVERT(CHAR(11), @myDate, 106)  
36. PRINT CONVERT(CHAR(12), @myDate, 107)  
37. PRINT CONVERT(CHAR(8),  @myDate, 108)  
38. PRINT CONVERT(CHAR(26), @myDate, 109)  
39. PRINT CONVERT(CHAR(10), @myDate, 110)  
40. PRINT CONVERT(CHAR(10), @myDate, 111)  
41. PRINT CONVERT(CHAR(8),  @myDate, 112)  
42. PRINT CONVERT(CHAR(24), @myDate, 113)  
43. PRINT CONVERT(CHAR(12), @myDate, 114)  
44. PRINT CONVERT(CHAR(19), @myDate, 120)  
45. PRINT CONVERT(CHAR(23), @myDate, 121)  
46. PRINT CONVERT(CHAR(23), @myDate, 126)  
47. PRINT CONVERT(CHAR(23), @myDate, 127)  
48. PRINT CONVERT(CHAR(32), @myDate, 130)  
49. PRINT CONVERT(CHAR(25), @myDate, 131)  

執行結果：


接下來看 CAST 的語法：

檢視原始檔複製到剪貼簿列印關於

1. CAST (運算式 AS 資料型別 [ (資料長度) ])  

與 CONVERT 相比，CAST 馬上就遜掉了，因為 CAST 沒有辦法指定日期格式樣式，如果要指定的話，勢必要自己加工過。
例如，我們要像 YYYY-MM-DD HH:MI:SS.sss 這樣的結果：

檢視原始檔複製到剪貼簿列印關於

1. -- 定義變數  
2. DECLARE @myDate DATETIME  
3. -- 指派一個日期給該變數  
4. SET @myDate = '2008/09/09 08:25 AM'  
5.   
6. -- 開始轉換  
7. PRINT '使用 CONVERT 轉換 ==> ' +  
8.     CONVERT(CHAR(23), @myDate, 21)  
9. PRINT '使用 CAST 轉換 =====> ' +  
10.     CAST(DATEPART(YY, @myDate) AS CHAR(4)) + '-'  
11.     + RIGHT(CAST(100 + DATEPART(MM, @myDate) AS CHAR(3)), 2) + '-'  
12.     + RIGHT(CAST(100 + DATEPART(DD, @myDate) AS CHAR(3)), 2) + ' '  
13.     + RIGHT(CAST(100 + DATEPART(HH, @myDate) AS CHAR(3)), 2) + ':'  
14.     + RIGHT(CAST(100 + DATEPART(MI, @myDate) AS CHAR(3)), 2) + ':'  
15.     + RIGHT(CAST(100 + DATEPART(SS, @myDate) AS CHAR(3)), 2) + '.'  
16.     + RIGHT(CAST(1000+ DATEPART(MS, @myDate) AS CHAR(4)), 3)  

執行結果：


接下來我們直接透過實際的程式碼，討論如何把 AM 或 PM 換成中文的上午或下午：

檢視原始檔複製到剪貼簿列印關於

1. DECLARE @myDate DATETIME  
2. SET @myDate = '2008/09/09 08:25 AM'  
3. SELECT CAST(DATEPART(YYYY, @myDate) AS CHAR(4)) + '/'   
4.     + RIGHT(CAST(100 + DATEPART(MM, @myDate) AS CHAR(3)), 2) + '/'  
5.     + RIGHT(CAST(100 + DATEPART(DD, @myDate) AS CHAR(3)), 2) + ' '  
6.     + CASE WHEN DATEPART(HH, @myDate) < 13  
7.         THEN RIGHT(CAST(100 + DATEPART(HH, @myDate) AS CHAR(3)), 2)  
8.         ELSE CAST(DATEPART(HH, @myDate) - 12 AS CHAR(2))  
9.     END + ':'  
10.     + RIGHT(CAST(100 + DATEPART(MI, @myDate) AS CHAR(3)), 2)  
11.     + CASE WHEN DATEPART(HH, @myDate) < 13  
12.         THEN '上午'  
13.         ELSE '下午'  
14.     END  

執行結果：


最後，就以轉換西元日期成為民國年作為最後一個轉換的範例吧：

檢視原始檔複製到剪貼簿列印關於

1. DECLARE @myDate DATETIME  
2. SET @myDate = '2008/09/09 08:25 AM'  
3. SELECT CAST(YEAR(@myDate) - 1911 AS NVARCHAR(3)) + '年'  
4.     + CAST(Month(@myDate) AS NVARCHAR(2)) + '月'  
5.     + CAST(Day(@myDate) AS NVARCHAR(2)) + '日'  

執行結果：