CAST 和 CONVERT (Transact-SQL)

當 expression 是日期或時間資料類型時， style 就可以是下表所列的其中一個值。其他值則當做 0 處理。SQL Server 利用科威特演算法來支援阿拉伯文樣式的日期格式。

1 這些樣式值會傳回不具決定性的結果。其中包括所有 (yy) (不含世紀) 樣式和 (yyyy) (含世紀) 樣式的子集。

2 預設值 (style0 或 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。許多用戶端應用程式 (如以 Automation 物件為基礎的用戶端應用程式) 都是以 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 轉換成字元資料時，包括秒或毫秒的樣式會在這些位置顯示零。當您從 datetime 或 smalldatetime 值轉換時，您可以利用適當的 char 或 varchar 資料類型長度來截斷不需要的日期部分。

當您從含有時間之樣式的字元資料轉換成 datetimeoffset 時，時區時差就會附加至結果。

當 expression 是 float 或 real 時， style 就可以是下表所列的其中一個值。其他值則當做 0 處理。

當 expression 是 money 或 smallmoney 時，style 就可以是下表所列的其中一個值。其他值則當做 0 處理。

當 expression 是 xml 時，, style就可以是下表所列的其中一個值。其他值則當做 0 處理。

值	輸出
0 (預設值)	使用預設剖析行為，捨棄無意義的空格，不允許內部 DTD 子集。 附註 當您轉換成 xml 資料類型時，SQL Server 無意義空格的處理方式與 XML 1.0 不同。如需詳細資訊，請參閱＜產生 XML 執行個體＞。
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 就可以是下表所列的其中一個值。此表中沒有列出的樣式值會傳回錯誤。

隱含的轉換是不需要指定 CAST 或 CONVERT 函數，便能夠進行的轉換。明確的轉換是需要指定 CAST 或 CONVERT 函數，才能進行的轉換。下圖顯示 SQL Server 系統提供的資料類型所能使用的所有明確和隱含的資料類型轉換。其中包括 xml、bigint 和 sql_variant。在指派時，沒有從 sql_variant 資料類型進行的隱含轉換，但有轉換成 sql_variant 的隱含轉換。

當您在 datetimeoffset 與字元類型 char、varchar、nchar 和 nvarchar 之間轉換時，轉換過的時區時差部分的 HH 和 MM 應該永遠是兩位數，例如 -08:00。

大數值資料類型會表現出與較小對應項目相同的隱含和明確轉換行為，明確地說，便是 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 Common Language Runtime (CLR) 使用者定義型別的資訊，請參閱＜對使用者自訂類型執行作業＞。如需有關如何轉換 xml 資料類型的詳細資訊，請參閱＜產生 XML 執行個體＞。

當您將 xml 資料類型明確或隱含地轉換為字串或二進位資料類型時，xml 資料類型的內容會根據一組規則來序列化。如需有關這些規則的資訊，請參閱＜XML 資料的序列化＞。如需有關如何從 XML 轉換為 CLR 使用者自訂類型的資訊，請參閱＜對使用者自訂類型執行作業＞。如需有關如何從其他資料類型轉換成 xml 資料類型的詳細資訊，請參閱＜產生 XML 執行個體＞。

不支援 text 和 image 資料類型進行自動資料類型轉換。您可以將 text 資料明確地轉換成字元資料，將 image 資料轉換成 binary 或 varbinary，但最大長度是 8000 位元組。如果您試圖進行不正確的轉換，例如將包括字母的字元運算式轉換成 int，SQL Server 會傳回一則錯誤訊息。

當 CAST 或 CONVERT 的輸出是字元字串，輸入也是字元字串時，輸出的定序和定序標籤與輸入相同。如果輸入不是字元字串，輸出會使用預設的資料庫定序及強制預設的定序標籤。如需詳細資訊，請參閱＜定序優先順序 (Transact-SQL)＞。

若要將不同的定序指派給輸出，請將 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));

下列範例會顯示因太小而無法顯示的結果運算式。

USE AdventureWorks2008R2;
GO
SELECT p.FirstName, p.LastName, SUBSTRING(p.Title, 1, 25) AS Title, CAST(e.SickLeaveHours AS char(1)) AS 'Sick Leave'
FROM HumanResources.Employee e 
JOIN Person.Person p ON e.BusinessEntityID = p.BusinessEntityID
WHERE NOT e.BusinessEntityID >5;

以下為結果集：

FirstName     LastName       Title       Sick Leave

---------     -------------  ----------  -----------

Ken           Sanchez        NULL        *

Terri         Duffy          NULL        *

Roberto       Tamburello     NULL        *

Rob           Walters        NULL        *

Gail          Erickson       Ms.         *

(5 row(s) affected)

當您轉換小數位數不同的資料類型時，有時會截斷結果值，有時會捨入結果值。下表說明這個行為。

例如，下列轉換的結果是 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 也會傳回一則錯誤。

在 SQL Server 2000 中，字串對日期和時間轉換會標示為具決定性。不過，這對於下表所列出的樣式並不適用。對於這些樣式而言，轉換取決於語言設定。SQL Server 2005 及更新的版本會將這些轉換標示為不具決定性。

下表列出字串對日期時間轉換不具決定性的樣式。

1 樣式 20 和 21 例外

每個範例都會擷取標價第一位數是 3 的產品名稱，且會將它們的 ListPrice 轉換成 int。

-- Use CAST
USE AdventureWorks2008R2;
GO
SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice
FROM Production.Product
WHERE CAST(ListPrice AS int) LIKE '3%';
GO

-- Use CONVERT.
USE AdventureWorks2008R2;
GO
SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice
FROM Production.Product
WHERE CONVERT(int, ListPrice) LIKE '3%';
GO

下列範例會將年度目前的總銷售額 (SalesYTD) 除以任務百分比 (CommissionPCT) 來計算單一資料行計算 (Computed)。這個結果在捨入到最近的整數之後，會轉換成 int 資料類型。

USE AdventureWorks2008R2;
GO
SELECT CAST(ROUND(SalesYTD/CommissionPCT, 0) AS int) AS 'Computed'
FROM Sales.SalesPerson 
WHERE CommissionPCT != 0;
GO

以下為結果集：

Computed

------

379753754

346698349

257144242

176493899

281101272

0

301872549

212623750

298948202

250784119

239246890

101664220

124511336

97688107

(14 row(s) affected)

下列範例會利用 CAST 來串連非字元、非二進位運算式。

USE AdventureWorks2008R2;
GO
SELECT 'The list price is ' + CAST(ListPrice AS varchar(12)) AS ListPrice
FROM Production.Product
WHERE ListPrice BETWEEN 350.00 AND 400.00;
GO

以下為結果集：

ListPrice

------------------

The list price is 357.06

The list price is 364.09

The list price is 364.09

The list price is 364.09

The list price is 364.09

(5 row(s) affected)

下列範例會利用選取清單中的 CAST 來將 Name 資料行轉換成 char(10) 資料行。

USE AdventureWorks2008R2;
GO
SELECT DISTINCT CAST(p.Name AS char(10)) AS Name, s.UnitPrice
FROM Sales.SalesOrderDetail s JOIN Production.Product p on s.ProductID = p.ProductID
WHERE Name LIKE 'Long-Sleeve Logo Jersey, M';
GO

以下為結果集：

Name       UnitPrice

---------- ---------------------

Long-Sleev 31.2437

Long-Sleev 32.4935

Long-Sleev 49.99

(3 row(s) affected)

下列範例會將 money 資料行 SalesYTD 轉換成 int，再轉換成 char(20) 資料行，以便能夠搭配 LIKE 子句來使用。

USE AdventureWorks2008R2;
GO
SELECT p.FirstName, p.LastName, s.SalesYTD, s.BusinessEntityID
FROM Person.Person p JOIN Sales.SalesPerson s ON p.BusinessEntityID = s.BusinessEntityID
WHERE CAST(CAST(s.SalesYTD AS int) AS char(20)) LIKE '2%';
GO

以下為結果集：

FirstName        LastName            SalesYTD         SalesPersonID

---------------- ------------------- ---------------- -------------

Tsvi             Reiter              2811012.7151      279

Syed             Abbas               219088.8836       288

Rachel           Valdez              2241204.0424      289

(3 row(s) affected)

下列範例會顯示如何利用 CONVERT 來轉換成 XML 類型 (使用＜xml 資料類型＞)。

這個範例會將含有空格、文字和標記的字串轉換成 XML 類型，且會移除所有無意義的空格 (節點之間的界限空格)：

CONVERT(XML, '<root><child/></root>')

這個範例會將含有空格、文字和標記的類似字串轉換成 XML 類型，且會保留所有無意義的空格 (節點之間的界限空格)：

CONVERT(XML, '<root>          <child/>         </root>', 1)

這個範例會將含有空格、文字和標記的字串轉換成 XML 類型：

CAST('<Name><FName>Carol</FName><LName>Elliot</LName></Name>'  AS XML)

如需其他範例，請參閱＜產生 XML 執行個體＞。

下列範例會顯示目前的日期和時間、使用 CAST 將目前的日期和時間變更成字元資料類型，然後使用 CONVERT 以 ISO 8901 格式顯示日期和時間。

SELECT 
   GETDATE() AS UnconvertedDateTime,
   CAST(GETDATE() AS nvarchar(30)) AS UsingCast,
   CONVERT(nvarchar(30), GETDATE(), 126) AS UsingConvertTo_ISO8601  ;
GO

以下為結果集：

UnconvertedDateTime     UsingCast                      UsingConvertTo_ISO8601

----------------------- ------------------------------ ------------------------------

2006-04-18 09:58:04.570 Apr 18 2006 9:58AM            2006-04-18T09:58:04.570

(1 row(s) affected)

下列範例幾乎是上一個範例的相反操作。這個範例會將日期和時間顯示成字元資料、使用 CAST 將字元資料變更成 datetime 資料類型，然後使用 CONVERT 將字元資料變更成 datetime 資料類型。

SELECT 
   '2006-04-25T15:50:59.997' AS UnconvertedText,
   CAST('2006-04-25T15:50:59.997' AS datetime) AS UsingCast,
   CONVERT(datetime, '2006-04-25T15:50:59.997', 126) AS UsingConvertFrom_ISO8601 ;
GO

以下為結果集：

UnconvertedText         UsingCast               UsingConvertFrom_ISO8601

----------------------- ----------------------- ------------------------

2006-04-25T15:50:59.997 2006-04-25 15:50:59.997 2006-04-25 15:50:59.997

(1 row(s) affected)

下列範例將顯示使用不同樣式來轉換二進位和字元資料的結果。

--Convert the binary value 0x4E616d65 to a character value.
SELECT CONVERT(char(8), 0x4E616d65, 0) AS 'Style 0, binary to character'

以下為結果集：

Style 0, binary to character

----------------------------

Name

(1 row(s) affected)

--The following example shows how Style 1 can force the result
--to be truncated. The truncation is caused by
--including the characters 0x in the result.
SELECT CONVERT(char(8), 0x4E616d65, 1) AS 'Style 1, binary to character'

以下為結果集：

Style 1, binary to character

------------------------------

0x4E616D

(1 row(s) affected)

--The following example shows that Style 2 does not truncate the
--result because the characters 0x are not included in
--the result.
SELECT CONVERT(char(8), 0x4E616d65, 2) AS 'Style 2, binary to character'

以下為結果集：

Style 2, binary to character

------------------------------

4E616D65

(1 row(s) affected)

--Convert the character value 'Name' to a binary value.
SELECT CONVERT(binary(8), 'Name', 0) AS 'Style 0, character to binary'

以下為結果集：

Style 0, character to binary

----------------------------------

0x4E616D6500000000

(1 row(s) affected)

SELECT CONVERT(binary(4), '0x4E616D65', 1) AS 'Style 1, character to binary'

以下為結果集：

Style 1, character to binary

----------------------------------

0x4E616D65

(1 row(s) affected)

SELECT CONVERT(binary(4), '4E616D65', 2) AS 'Style 2, character to binary'

以下為結果集：

Style 2, character to binary

----------------------------------

0x4E616D65

(1 row(s) affected)