SQL Server Cast、Convert數據類型轉換

一、概述

本篇文章轉載來著官網線上文檔，文章主要介紹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 樣式

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 進行處理。

money 和 smallmoney 樣式

如果 expression 為 money 或 smallmoney，則 style 可以為下表中顯示的值之一。其他值作為 0 進行處理。

xml 樣式

如果 data_type 為 xml，則 style 可以為下表中顯示的值之一。其他值作為 0 進行處理。

二進位樣式

如果 expression 為 binary(n)、varbinary(n)、char(n) 或 varchar(n)，則 style 可以為下表中顯示的值之一。表中沒有列出的樣式值將返回錯誤。

隱式轉換

隱式轉換指那些沒有指定 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 的轉換都將被截斷。

* = 結果長度太短而無法顯示。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 的各個版本中都相同。

轉換小數位數不同的數據類型時，結果值有時被截斷，有時被舍入。下表顯示了此行為。

例如，以下轉換的結果為 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 也返回錯誤。

備註：文章轉載來自官方線上文檔。