SQL Server Cast、Convert資料類型轉換
2018-03-16 16:54
pursuer.chen
閱讀(12801)
評論(0)
編輯
收藏
舉報
一、概述
本篇文章轉載來着官網線上文檔，文章主要介紹SQL Server資料類型轉換相關文法、隐式轉換、Date樣式等。
文法
Syntax for CAST:
CAST ( expression AS data_type [ ( length ) ] )
Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] ) 參數
expression：任何有效的表達式。
- data_type：目标資料類型。這包括 xml、bigint 和 sql_variant。不能使用别名資料類型。有關可用資料類型的詳細資訊，請參閱資料類型 (Transact-SQL)。
- length：指定目标資料類型長度的可選整數。預設值為 30。
- style：指定 CONVERT 函數如何轉換 expression 的整數表達式。如果樣式為 NULL，則傳回 NULL。該範圍是由 data_type 确定的。有關詳細資訊，請參閱“備注”部分。
Date 和 Time 樣式
如果 expression 為 date 或 time 資料類型，則 style 可以為下表中顯示的值之一。其他值作為 0 進行處理。SQL Server 使用科威特算法來支援阿拉伯樣式的日期格式。
| 不帶世紀數位 (yy) (1) | 帶世紀數位 (yyyy) | 标準 | 輸入/輸出 (3) |
|---|---|---|---|
| - | 0 或 100 (1,2) | 預設 | mon dd yyyy hh:miAM（或 PM） |
| 1 | 101 | 美國 | mm/dd/yyyy |
| 2 | 102 | ANSI | yy.mm.dd |
| 3 | 103 | 英國/法國 | dd/mm/yyyy |
| 4 | 104 | 德國 | dd.mm.yy |
| 5 | 105 | 意大利 | dd-mm-yy |
| 6 | 106 (1) | - | dd mon yy |
| 7 | 107 (1) | - | mon dd, yy |
| 8 | 108 | - | hh:mi:ss |
| - | 9 或 109 (1,2) | 預設設定 + 毫秒 | mon dd yyyy hh:mi:ss:mmmAM（或 PM） |
| 10 | 110 | 美國 | mm-dd-yy |
| 11 | 111 | 日本 | yy/mm/dd |
| 12 | 112 | ISO | yymmdd yyyymmdd |
| - | 13 或 113 (1,2) | 歐洲預設設定 + 毫秒 | dd mon yyyy hh:mi:ss:mmm(24h) |
| 14 | 114 | - | hh:mi:ss:mmm(24h) |
| - | 20 或 120 (2) | ODBC 規範 | yyyy-mm-dd hh:mi:ss(24h) |
| - | 21 或 121 (2) | ODBC 規範（帶毫秒） | yyyy-mm-dd hh:mi:ss.mmm(24h) |
| - | 126 (4) | ISO8601 | yyyy-mm-ddThh:mi:ss.mmm（無空格） |
| - | 127(6, 7) | 帶時區 Z 的 ISO8601。 | yyyy-mm-ddThh:mi:ss.mmmZ （無空格） |
| - | 130 (1,2) | 回曆 (5) | dd mon yyyy hh:mi:ss:mmmAM |
| - | 131 (2) | 回曆 (5) | dd/mm/yy hh:mi:ss:mmmAM |
1 這些樣式值将傳回不确定的結果。包括所有 (yy)（不帶世紀數位）樣式和一部分 (yyyy)（帶世紀數位）樣式。
2 預設值（style 0 或 100、9 或 109、13 或 113、20 或 120 以及 21 或 121）始終傳回世紀數位 (yyyy)。
3 轉換為 datetime 時輸入；轉換為字元資料時輸出。
4 為用于 XML 而設計。對于從 datetime 或 smalldatetime 到字元資料的轉換，其輸出格式如上一個表所述。
5 回曆是有多種變體的月曆系統。SQL Server 使用科威特算法。
注意：預設情況下，SQL Server 基于截止年份 2049 年來解釋兩位數的年份。換言之，就是将兩位數的年份 49 解釋為 2049，将兩位數的年份 50 解釋為 1950。許多用戶端應用程式（如基于自動化對象的應用程式）都使用截止年份 2030 年。SQL Server 提供了 two digit year cutoff 配置選項，可通過此選項更改 SQL Server 使用的截止年份，進而對日期進行一緻處理。建議您指定四位數年份。
6 僅支援從字元資料轉換為 datetime 或 smalldatetime。僅表示日期或時間成分的字元資料轉換為 datetime 或 smalldatetime 資料類型時，未指定的時間成分設定為 00:00:00.000，未指定的日期成分設定為 1900-01-01。
7使用可選的時間區域訓示符 (Z) 更便于将具有時區資訊的 XML datetime 值映射到沒有時區的 SQL Server datetime 值。Z 是時區 UTC-0 的訓示符。其他時區則以 + 或 - 方向的 HH:MM 偏移量來訓示。例如：
2006-12-12T23:45:12-08:00
。
從 smalldatetime 轉換為字元資料時，包含秒或毫秒的樣式将在這些位置上顯示零。使用相應的 char 或 varchar 資料類型長度從 datetime 或 smalldatetime 值轉換時，可截斷不需要的日期部分。
從樣式包含時間的字元資料轉換為 datetimeoffset 時，将在結果末尾追加時區偏移量。
float 和 real 樣式
如果 expression 為 float 或 real，則 style 可以為下表中顯示的值之一。其他值作為 0 進行處理。
| 值 | 輸出 |
|---|---|
| 0（預設值） | 最多包含 6 位。根據需要使用科學記數法。 |
| 1 | 始終為 8 位值。始終使用科學記數法。 |
| 2 | 始終為 16 位值。始終使用科學記數法。 |
| 126, 128, 129 | 為了保持向後相容而包括在内，在以後的版本中可能不推薦使用。 |
money 和 smallmoney 樣式
如果 expression 為 money 或 smallmoney，則 style 可以為下表中顯示的值之一。其他值作為 0 進行處理。
| 值 | 輸出 |
|---|---|
| 0（預設值） | 小數點左側每三位數字之間不以逗号分隔，小數點右側取兩位數，例如 4235.98。 |
| 1 | 小數點左側每三位數字之間以逗号分隔，小數點右側取兩位數，例如 3,510.92。 |
| 2 | 小數點左側每三位數字之間不以逗号分隔，小數點右側取四位數，例如 4235.9819。 |
| 126 | 轉換為 char(n) 或 varchar(n) 時，等同于樣式 2 |
xml 樣式
如果 data_type 為 xml，則 style 可以為下表中顯示的值之一。其他值作為 0 進行處理。
| 值 | 輸出 | ||
| 0（預設值） | 使用預設的分析行為，即放棄無用的空格，且不允許使用内部 DTD 子集。
| ||
| 1 | 保留無用空格。此樣式設定将預設的 xml:space 處理方式設定為與指定了 xml:space="preserve" 的行為相同。 | ||
| 2 | 啟用有限的内部 DTD 子集處理。 如果啟用，則伺服器可使用内部 DTD 子集提供的以下資訊來執行非驗證分析操作。 應用屬性的預設值。 解析并擴充内部實體引用。 檢查 DTD 内容模型以實作文法的正确性。 分析器将忽略外部 DTD 子集。此外，不評估 XML 聲明來檢視 standalone 屬性是設定為 yes 還是 no，而是将 XML 執行個體當成一個獨立文檔進行分析。 | ||
| 3 | 保留無用空格，并啟用有限的内部 DTD 子集處理。 |
二進制樣式
如果 expression 為 binary(n)、varbinary(n)、char(n) 或 varchar(n)，則 style 可以為下表中顯示的值之一。表中沒有列出的樣式值将傳回錯誤。
| 值 | 輸出 |
|---|---|
| 0（預設值） | 将 ASCII 字元轉換為二進制位元組，或者将二進制位元組轉換為 ASCII 字元。每個字元或位元組按照 1:1 進行轉換。 如果 data_type 為二進制類型，則會在結果左側添加字元 0x。 |
| 1, 2 | 如果 data_type 為二進制類型，則表達式必須為字元表達式。expression 必須由數量為偶數的十六進制數字（0、1、2、3、4、5、6、7、8、9、A、B、C、D、E、F、a、b、c、d、e、f）組成。如果将 style 設定為 1，字元 0x 必須為表達式中的前兩個字元。如果表達式中包含的字元數為奇數或者包含任何無效的字元，則會引發錯誤。 如果轉換後的表達式長度大于 data_type 長度，則會在右側截斷結果。 如果固定長度 data_types 大于轉換後的結果，則會在結果右側添加零。 如果 data_type 為字元類型，則表達式必須為二進制表達式。每個二進制字元均轉換為兩個十六進制字元。如果轉換後的表達式長度大于 data_type 長度，則會在右側截斷結果。 如果 data_type 為固定大小的字元類型，并且轉換後的結果長度小于其 data_type 長度，則會在轉換後的表達式右側添加空格，以使十六進制數字的個數保持為偶數。 對于 style 1，将在轉換後的結果左側添加字元 0x。 |
隐式轉換
隐式轉換指那些沒有指定 CAST 或 CONVERT 函數的轉換。顯式轉換指那些需要指定 CAST 或 CONVERT 函數的轉換。以下圖例顯示了可對 SQL Server 系統提供的資料類型執行的所有顯式和隐式資料類型轉換。其中包括 xml、bigint 和 sql_variant。不存在對 sql_variant 資料類型的指派進行的隐式轉換，但是存在轉換為 sql_variant 的隐式轉換。
在 datetimeoffset 與字元類型 char、varchar、nchar 和 nvarchar 之間轉換時，轉換後的時區偏移量部分的 HH 和 MM 都應始終為兩個數字，例如 -08:00。
注意：因為 Unicode 資料始終使用偶數個位元組，是以在 binary 或 varbinary 與支援 Unicode 的資料類型之間進行轉換時會使用警告。例如，以下轉換不傳回十六進制值 41；而是傳回 4100：
SELECT CAST(CAST(0x41 AS nvarchar) AS varbinary)
。
大值資料類型
大值資料類型表現出與小值資料類型相同的隐式和顯式轉換行為，特别是 varchar、nvarchar 和 varbinary 資料類型。但是，應該考慮以下原則：
- 從 image 到 varbinary(max) 的轉換與反向轉換是隐式轉換，text 與 varchar(max)、ntext、nvarchar(max) 之間的轉換也是隐式轉換。
-
從大值資料類型（如 varchar(max)）到小值資料類型（如
varchar）的轉換是隐式轉換，但如果大值相對于指定長度的小值資料類型顯得太大，則産生截斷。
-
從 varchar、nvarchar 或 varbinary
到其相應的大值資料類型的轉換都是隐式執行的。
- 從 sql_variant 資料類型到大值資料類型的轉換是顯式轉換。
- 大值資料類型不能轉換為 sql_variant 資料類型。
有關如何轉換 Microsoft .NET Framework 公共語言運作時 (CLR)
使用者定義類型的資訊，請參閱對使用者定義類型執行操作。有關如何從
xml 資料類型進行轉換的詳細資訊。
xml 資料類型
當您将 xml 資料類型顯式或隐式轉換為字元串或二進制資料類型時，xml
資料類型的内容将根據一組規則進行序列化。有關這些規則的資訊，請參閱 XML 資料的序列化。有關如何從
XML 轉換為 CLR 使用者定義類型的資訊，請參閱對使用者定義類型執行操作。有關如何從其他資料類型轉換到
xml 資料類型的資訊。
文本和圖像資料類型
不支援對 text 和 image 資料類型進行自動資料類型轉換。可将 text
資料顯式轉換為字元資料，将 image 資料轉換為 binary 或 varbinary，但最大長度是 8000
位元組。如果試圖進行不正确的轉換，如将包含字母的字元表達式轉換為 int，則 SQL Server
将傳回錯誤消息。
輸出排序規則
如果 CAST 或 CONVERT
的輸出是字元串，并且輸入也是字元串，則輸出将與輸入具有相同的排序規則和排序規則标簽。如果輸入不是字元串，則輸出采用資料庫的預設排序規則以及強制預設的排序規則标簽。
若要為輸出配置設定不同的排序規則，請将 COLLATE 子句應用于 CAST 或 CONVERT 函數的結果表達式。例如：
SELECT CAST(\'abc\' AS varchar(5)) COLLATE French_CS_AS
截斷結果和舍入結果
将字元或二進制表達式（char、nchar、nvarchar、varchar、binary 或 varbinary）轉換為其他資料類型的表達式時，可截斷資料，僅顯示部分資料，或傳回錯誤（因為結果太短而無法顯示）。除了下表顯示的轉換，其他到 char、varchar、nchar、nvarchar、binary 和 varbinary 的轉換都将被截斷。
| 被轉換的資料類型 | 轉換為的資料類型 | 結果 |
|---|---|---|
| int、smallint 或 tinyint | char | * |
| varchar | * | |
| nchar | E | |
| nvarchar | E | |
| money、smallmoney、numeric、decimal、float 或 real | char | E |
| varchar | E | |
| nchar | E | |
| nvarchar | E |
* = 結果長度太短而無法顯示。E = 因為結果長度太短無法顯示而傳回錯誤。
SQL Server 僅保證往返轉換（即從原始資料類型進行轉換後又傳回原始資料類型的轉換）在各版本間産生相同值。以下示例顯示的即是這樣的往返轉換：
DECLARE @myval decimal (5, 2)
SET @myval = 193.57
SELECT CAST(CAST(@myval AS varbinary(20)) AS decimal(10,5))
-- Or, using CONVERT
SELECT CONVERT(decimal(10,5), CONVERT(varbinary(20), @myval))
注意：不要嘗試構造 binary 值然後将其轉換為數值資料類型類别的一種資料類型。SQL Server 不能保證 decimal 或 numeric 資料類型到 binary 的轉換結果在 SQL Server 的各個版本中都相同。
轉換小數位數不同的資料類型時，結果值有時被截斷，有時被舍入。下表顯示了此行為。
| 被轉換的資料類型 | 轉換到的資料類型 | 行為 |
|---|---|---|
| numeric | numeric | 舍入 |
| numeric | int | 截斷 |
| numeric | money | 舍入 |
| money | int | 舍入 |
| money | numeric | 舍入 |
| float | int | 截斷 |
| float | numeric | 舍入 |
| float | datetime | 舍入 |
| datetime | int | 舍入 |
例如，以下轉換的結果為
10
：
SELECT CAST(10.6496 AS int) 在進行資料類型轉換時，若目标資料類型的小數位數小于源資料類型的小數位數，則該值将被截斷。例如，以下轉換的結果為
$10.3497
：
SELECT CAST(10.3496847 AS money) 當非數字型 char、nchar、varchar 或 nvarchar 資料轉換為 int、float、numeric 或 decimal 時，SQL Server 将傳回錯誤消息。當空字元串 (" ") 轉換為 numeric 或 decimal 時，SQL Server 也傳回錯誤。
備注：文章轉載來自官方線上文檔。
| 備注： 作者：pursuer.chen 部落格：http://www.cnblogs.com/chenmh 本站點所有随筆都是原創，歡迎大家轉載；但轉載時必須注明文章來源，且在文章開頭明顯處給明連結，否則保留追究責任的權利。 《歡迎交流讨論》 |
- 分類 SQLServer 基礎