CAST 和 CONVERT (Transact-SQL)
適用於：
SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體 Azure Synapse Analytics Analytics Platform System (PDW) Microsoft Fabric 的 SQL 端點分析 Microsoft Fabric 的倉儲
這些函式會將某種資料類型的運算式轉換成另一種資料類型。
語法
CAST 語法：
CAST ( expression AS data_type [ ( length ) ] )
CONVERT 語法：
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
引數
expression
任何有效的運算式。
data_type
目標資料類型。 這包括 xml、bigint 和 sql_variant。 無法使用別名資料類型。
length
可指定目標資料類型長度的選擇性整數，用於允許使用者指定長度的資料類型。 預設值是 30。
style
整數運算式，指定 CONVERT 函數如何轉譯 expression。 針對樣式值 NULL，會傳回 NULL。 data_type 可決定範圍。
傳回類型
傳回轉譯為 data_type 的 expression。
日期和時間樣式
對於日期或時間資料類型的 expression，style 可以具有下表所示的其中一個值。 其他值則當做 0 處理。 從 SQL Server 2012 (11.x) 開始，從日期和時間類型轉換成 datetimeoffset 時，支援的樣式只有 0 或 1。 所有其他轉換樣式都會傳回錯誤 9809。
注意
SQL Server 利用科威特演算法來支援阿拉伯文樣式的日期格式。
| 不含世紀 (yy) 1 | 含世紀 (yyyy) | 標準 | 輸入/輸出 3 |
|---|---|---|---|
| - | 0 或 100 1,2 | datetime 和 smalldatetime 的預設 | mon dd yyyy hh:miAM (或 PM) |
| 1 | 101 | 美式英文 | 1 = mm/dd/yy101 = mm/dd/yyyy |
| 2 | 102 | ANSI | 2 = yy.mm.dd102 = yyyy.mm.dd |
| 3 | 103 | 英式英文/法文 | 3 = dd/mm/yy103 = dd/mm/yyyy |
| 4 | 104 | 德文 | 4 = dd.mm.yy104 = dd.mm.yyyy |
| 5 | 105 | 義大利文 | 5 = dd-mm-yy105 = dd-mm-yyyy |
| 6 | 106 1 | - | 6 = dd mon yy106 = dd mon yyyy |
| 7 | 107 1 | - | 7 = Mon dd, yy107 = Mon dd, yyyy |
| 8 或 24 | 108 | - | hh:mi:ss |
| - | 9 或 109 1、2 | 預設值 + 毫秒 | mon dd yyyy hh:mi:ss:mmmAM (或 PM) |
| 10 | 110 | USA | 10 = mm-dd-yy 110 = mm-dd-yyyy |
| 11 | 111 | 日本 | 11 = yy/mm/dd 111 = yyyy/mm/dd |
| 12 | 112 | ISO | 12 = yymmdd 112 = yyyymmdd |
| - | 13 或 113 1、 2 | 歐洲預設值 + 毫秒 | dd mon yyyy hh:mi:ss:mmm (24 小時制) |
| 14 | 114 | - | hh:mi:ss:mmm (24 小時制) |
| - | 20 或 120 2 | ODBC 標準 | yyyy-mm-dd hh:mi:ss (24 小時制) |
| - | 21 或 25 或 121 2 | time、date、datetime2 和 datetimeoffset 的 ODBC 標準 (包含毫秒) 預設 | yyyy-mm-dd hh:mi:ss.mmm (24 小時制) |
| 22 | - | 美式英文 | mm/dd/yy hh:mi:ss AM (或 PM) |
| - | 23 | ISO8601 | yyyy-mm-dd |
| - | 126 4 | ISO8601 | yyyy-mm-ddThh:mi:ss.mmm (無空格) 6 |
| - | 127 8， 9 | ISO8601，包含時區 Z | yyyy-MM-ddThh:mm:ss.fffZ (無空格) 6 |
| - | 130 1， 2 | 回曆 5 | dd mon yyyy hh:mi:ss:mmmAM7 |
| - | 131 2 | 回曆 5 | dd/mm/yyyy hh:mi:ss:mmmAM |
1 這些樣式值會傳回不具決定性的結果。 包括所有 (yy) (不含世紀) 樣式和 (yyyy) (含世紀) 樣式的子集。
2 預設值 (0 或 100、9 或 109、13 或 113、20 或 120、23 及 21 或 25 或 121) 一律會傳回世紀 (yyyy)。
重要
根據預設，SQL Server 會根據截止年份 2049 來解譯兩位數的年份。 這表示 SQL Server 會將兩位數年份 49 解譯為 2049，而將兩位數年份 50 解譯為 1950。 許多用戶端應用程式 (包含以 Automation 物件為基礎的應用程式) 都使用截止年份 2030。 SQL Server 提供兩位數年份截止組態選項，以變更 SQL Server 所使用的截止年份。 這允許一致的日期處理。 我們建議您指定四位數的年份。
3 當轉換成 datetime 時輸入；當轉換成字元資料時輸出。
4 專為 XML 設計。 若要從 datetime 或 smalldatetime 轉換成字元資料，請參閱上表中的輸出格式。
5 阿拉伯回曆是具有多種變化的日曆系統。 SQL Server 使用科威特演算法。
6 若毫秒 (mmm) 值為 0，不會顯示毫秒十進位小數值。 例如，值 2022-11-07T18:26:20.000 會顯示為 2022-11-07T18:26:20。
7 在此樣式中，mon 代表完整月份名稱的多 Token 回曆 Unicode 表示法。 這個值無法在 SSMS 的預設美國安裝中正確轉譯。
8 只有在從字元資料轉換為 datetime 或 smalldatetime 時才支援。 將只代表日期或只代表時間元件的字元資料轉換成 datetime 或 smalldatetime 資料類型時，未指定的時間元件會設定為 00:00:00.000，而未指定的日期元件則會設定為 1900-01-01。
9 使用選擇性時區指標 Z，更輕鬆地將具有時區資訊的 XML datetime 值對應到沒有時區的 SQL Server datetime 值。 Z 表示時區為 UTC-0。 以 + 或 - 方向位移的 HH:MM 表示其他時區。 例如： 2022-12-12T23:45:12-08:00 。
將 smalldatetime 轉換成字元資料時，包含秒或毫秒的樣式會在這些位置顯示零。 從 datetime 或 smalldatetime 值轉換時，請使用適當的 char 或 varchar 資料類型長度來截斷不需要的日期部分。
使用包含時間的樣式將字元資料轉換成 datetimeoffset 時，會將時區位移附加至結果。
float 和 real 樣式
針對 float 或 real 運算式，style 可以有下表所示的其中一個值。 其他值則當做 0 處理。
| 值 | 輸出 |
|---|---|
| 0 (預設) | 最多 6 位數。 在適當時機，供科學記號標記法使用。 |
| 1 | 一律 8 位數。 一律用在科學記號標記法中。 |
| 2 | 一律 16 位數。 一律用在科學記號標記法中。 |
| 3 | 一律 17 位數。 用於不失真的轉換。 透過這個樣式，可保證將每個不同的 float 或 real 值轉換成相異的字元字串。 適用於：SQL Server 2016 (13.x) 和更新版本，以及 Azure SQL Database。 |
| 126、128、129 | 為舊版納入。 請勿將這些值用於新的開發。 |
money 和 smallmoney 樣式
針對 money 或 smallmoney 表達式，style 可以有下表所示的其中一個值。 其他值則當做 0 處理。
| 值 | 輸出 |
|---|---|
| 0 (預設) | 小數點左側並不會每隔三位數加一個逗號，小數點右側則會有兩位數 範例：4235.98。 |
| 1 | 小數點左側會每隔三位數加一個逗號，小數點右側則會有兩位數 範例：3,510.92。 |
| 2 | 小數點左側並不會每隔三位數加一個逗號，小數點右側則會有四位數 範例：4235.9819。 |
| 126 | 轉換成 Char(n) 或 varchar(n) 時，相當於樣式 2 |
xml 樣式
針對 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 子集處理。 |
二進位樣式
對於 binary（n）、char（n）、varbinary（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_type 會在結果的右側新增零。 字元類型的 data_type 需要二進位運算式。 每個二進位字元都會轉換成兩個十六進位字元。 假設所轉換運算式的長度超過 data_type 的長度。 在此情況下，將會截斷其長度。 針對固定大小字元類型 data_type，如果已轉換結果的長度小於 data_type 的長度，則會在所轉換運算式的右側新增空格，以維持偶數個十六進位數字。 字元 0x 不會新增到 style 2 轉換結果的左側。 |
隱含的轉換
隱含轉換不需要指定 CAST 函數或 CONVERT 函數。 明確轉換需要指定 CAST 函數或 CONVERT 函數。 下圖顯示 SQL Server 系統提供之資料類型所能使用的所有明確與隱含資料類型轉換。 這些包含 bigint、sql_variant 和 xml。 從 sql_variant 資料類型進行指派時，不可使用隱含轉換，但可以隱含轉換成 sql_variant。
提示
Microsoft 下載中心提供此圖表，可下載為 PNG 檔案。
上表標明 SQL Server 中允許的所有明確和隱含轉換，但是轉換的結果資料類型取決於執行的作業：
- 針對明確轉換，陳述式本身便會判斷結果資料類型。
- 針對隱含轉換，指派陳述式 (例如設定變數值或將值插入資料行) 會產生變數宣告或資料行定義所定義的資料類型。
- 針對比較運算子或其他運算式，結果資料類型將取決於資料類型優先順序的規則。
提示
本節稍後會有轉換中資料類型優先順序效果的實際範例。
當您在 datetimeoffset 與字元類型 Char、Nchar、Nvarchar 和 varchar 之間轉換時，轉換過之時區位移部分的 HH 和 MM 應一律是兩位數。 例如： -08:00 。
由於 Unicode 資料使用的位元組數目一律是偶數，因此，在 binary 或 varbinary 和 Unicode 支援的資料類型之間轉換時，要特別小心。 例如，下列轉換不會傳回十六進位值 41， 而是傳回十六進位值 4100：
SELECT CAST(CAST(0x41 AS nvarchar) AS varbinary);
如需詳細資訊，請參閱 Collation and Unicode Support。
大數值資料類型
大數值資料類型具有與小數值資料類型相同的隱含和明確轉換行為，明確地說，就是 nvarchar、varbinary 和 varchar 資料類型。 不過，請考慮下列指導方針：
- 從 image 轉換成 varbinary(max) (反之亦然) 是一種隱含轉換，而 text 與 varchar(max) 以及 ntext 與 nvarchar(max) 之間的轉換亦是如此。
- 從大數值資料類型 (如 varchar(max) ) 轉換成較小的對應資料類型 (如 varchar) 是隱含的轉換，但如果大數值的大小超過較小資料類型的指定長度，則會予以截斷。
- 從 nvarchar、varbinary 或 varchar 轉換成其對應的大數值資料類型是透過隱含的方式進行。
- 從 sql_variant 資料類型轉換為大數值資料類型為明確的轉換。
- 大數值資料類型無法轉換成 sql_variant 資料類型。
如需從 xml 資料類型轉換的詳細資訊，請參閱建立 XML 資料的執行個體。
xml 資料型別
當您將 xml 資料類型明確或隱含地轉換成字串或二進位資料類型時，xml 資料類型的內容會根據定義的一組規則進行序列化。 如需如需這些規則的相關資訊，請參閱定義 XML 資料的序列化。 如需從其他資料類型轉換成 xml 資料類型的資訊，請參閱建立 XML 資料的執行個體。
text 和 image 資料類型
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;
截斷和捨入結果
將字元或二進位運算式 (binary、char、nchar、nvarchar、varbinary 或 varchar) 轉換成不同資料類型的運算式時，轉換作業可能會截斷輸出資料、僅局部顯示輸出資料，或傳回錯誤。 如果結果太短無法顯示，則會發生這些情況。 會截斷轉換成 binary、char、nchar、nvarchar、varbinary 或 varchar (下表中所顯示的轉換除外)。
| 來源資料類型 | 目標資料類型 | 結果 |
|---|---|---|
| int、smallint 或 tinyint | char varchar |
太短而無法顯示 |
| nchar nvarchar |
錯誤 1 | |
| money、smallmoney、numeric、decimal、float 或 real | char varchar |
錯誤 1 |
| nchar nvarchar |
錯誤 1 |
1 因結果長度太短無法顯示，而傳回錯誤。
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));
GO
警告
請不要建構 binary 值後，將其轉換成數值資料類型類別的資料類型。 SQL Server 不保證在不同的 SQL Server 版本之間，將 decimal 或 numeric 資料類型轉換成 binary 的結果都會相同。
下列範例會顯示因太小而無法顯示的結果運算式。
USE AdventureWorks2022;
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
INNER JOIN Person.Person p
ON e.BusinessEntityID = p.BusinessEntityID
WHERE NOT e.BusinessEntityID > 5;
GO
結果集如下所示。
FirstName LastName Title Sick Leave
--------- ------------- ------- --------`
Ken Sanchez NULL *
Terri Duffy NULL *
Roberto Tamburello NULL *
Rob Walters NULL *
Gail Erickson Ms. *
(5 row(s) affected)
當您轉換小數位數不同的資料類型時，SQL Server 有時會傳回截斷的結果值，有時會傳回捨入的值。 此表格顯示這個行為。
| 從 | 至 | 行為 |
|---|---|---|
| numeric | numeric | Round |
| numeric | int | Truncate |
| numeric | money | Round |
| money | int | Round |
| money | numeric | Round |
| float | int | Truncate |
| float | numeric | 四捨五入 1 |
| float | datetime | Round |
| datetime | int | Round |
1 如果您將使用科學記號標記法的 float 值轉換成 decimal 或 numeric，就會限制為只有 17 個有效位數的值。 有效位數超過 17 的任何值都會捨入為零。
例如，10.6496 和-10.6496 這兩個值可能會被截斷，或在轉換成 int 或 numeric 類型期間被捨入：
SELECT CAST(10.6496 AS INT) AS trunc1,
CAST(-10.6496 AS INT) AS trunc2,
CAST(10.6496 AS NUMERIC) AS round1,
CAST(-10.6496 AS NUMERIC) AS round2;
下表將顯示該查詢的結果：
| trunc1 | trunc2 | round1 | round2 |
|---|---|---|---|
| 10 | -10 | 11 | -11 |
轉換目標資料類型的小數位數小於來源資料類型的資料類型時，會將值捨入。 例如，這項轉換會傳回 $10.3497：
SELECT CAST(10.3496847 AS money);
將非數值 Char、Nchar、Nvarchar 或 varchar 資料轉換成 decimal、float、int 或 numeric 時，SQL Server 會傳回錯誤訊息。 當空字串 (" ") 轉換為 numeric 或 decimal 時，SQL Server 也會傳回錯誤。
某些日期時間轉換不具決定性
字串對日期時間轉換不具決定性的樣式如下所示：
- 100 以下的所有樣式1
- 106
- 107
- 109
- 113
- 130
1 樣式 20 和 21 除外
如需詳細資訊，請參閱將常值日期字串轉換成 DATE 值的非決定性轉換。
補充字元 (代理字組)
從 SQL Server 2012 (11.x) 開始，使用補充字元 (SC) 定序時，從 Nchar 或 Nvarchar 到較小長度之 Nchar 或 Nvarchar 類型的 CAST 作業不會在代理字組內截斷。 相反地，作業會在補充字元之前截斷。 例如，下列程式碼片段會讓 @x 只保留 'ab'。 空間不足，無法保留補充字元。
DECLARE @x NVARCHAR(10) = 'ab' + NCHAR(0x10000);
SELECT CAST(@x AS NVARCHAR(3));
使用 SC 定序時，CONVERT 的行為類似 CAST 的行為。 如需詳細資訊，請參閱定序和 Unicode 支援 - 增補字元。
相容性支援
在舊版 SQL Server 中，time 和 datetime2 資料類型的 CAST 和 CONVERT 作業預設樣式為 121，但任一類型用於計算資料行運算式時除外。 若為計算資料行，預設樣式為 0。 當您建立計算資料行、將它們用於包含自動參數化的查詢或用於條件約束定義時，這種行為就會影響計算資料行。
在相容性層級高於及等於 110 的情況下，time 和 datetime2 資料類型的 CAST 和 CONVERT 作業預設樣式一律為 121。 如果查詢仰賴舊的行為，則請使用低於 110 的相容性層級，或在受影響的查詢中明確指定 0 樣式。
| 相容性層級值 | 和 CONVERT 1 的預設樣式CAST |
計算資料行的預設樣式 |
|---|---|---|
| <110 | 121 | 0 |
| > = 110 | 121 | 121 |
1 計算資料行除外
將資料庫升級為相容性層級高於及等於 110，並不會變更已經儲存至磁碟的使用者資料。 您必須依適當情況手動更正這項資料。 例如，如果您已使用 SELECT INTO，根據包含上述計算資料行運算式的來源建立資料表，則系統會儲存資料 (使用樣式 0) 而非計算資料行定義本身。 您必須手動更新這項資料，以符合樣式 121。
範例
A. 使用 CAST 和 CONVERT
這些範例會擷取定價第一位數是 3 的產品名稱，並將其 ListPrice 值轉換成 int。
使用 CAST：
USE AdventureWorks2022;
GO
SELECT SUBSTRING(Name, 1, 30) AS ProductName,
ListPrice
FROM Production.Product
WHERE CAST(ListPrice AS INT) LIKE '33%';
GO
使用 CONVERT：
USE AdventureWorks2022;
GO
SELECT SUBSTRING(Name, 1, 30) AS ProductName,
ListPrice
FROM Production.Product
WHERE CONVERT(INT, ListPrice) LIKE '33%';
GO
結果集如下所示。 CAST 和 CONVERT 的範例結果集都相同。
ProductName ListPrice
------------------------------ ---------------------
LL Road Frame - Black, 58 337.22
LL Road Frame - Black, 60 337.22
LL Road Frame - Black, 62 337.22
LL Road Frame - Red, 44 337.22
LL Road Frame - Red, 48 337.22
LL Road Frame - Red, 52 337.22
LL Road Frame - Red, 58 337.22
LL Road Frame - Red, 60 337.22
LL Road Frame - Red, 62 337.22
LL Road Frame - Black, 44 337.22
LL Road Frame - Black, 48 337.22
LL Road Frame - Black, 52 337.22
Mountain-100 Black, 38 3374.99
Mountain-100 Black, 42 3374.99
Mountain-100 Black, 44 3374.99
Mountain-100 Black, 48 3374.99
HL Road Front Wheel 330.06
LL Touring Frame - Yellow, 62 333.42
LL Touring Frame - Blue, 50 333.42
LL Touring Frame - Blue, 54 333.42
LL Touring Frame - Blue, 58 333.42
LL Touring Frame - Blue, 62 333.42
LL Touring Frame - Yellow, 44 333.42
LL Touring Frame - Yellow, 50 333.42
LL Touring Frame - Yellow, 54 333.42
LL Touring Frame - Yellow, 58 333.42
LL Touring Frame - Blue, 44 333.42
HL Road Tire 32.60
(28 rows affected)
B. 搭配算術運算子使用 CAST
此範例會將年度目前的總銷售額 (SalesYTD) 除以佣金百分比 (CommissionPCT) 來計算單一資料行 (Computed)。 此值會四捨五入到最接近的整數，接著 CAST 為 int 資料類型。
USE AdventureWorks2022;
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)
C. 使用 CAST 串連
此範例會使用 CAST 串連非字元運算式。 其會使用 AdventureWorksDW2022 資料庫。
SELECT 'The list price is ' + CAST(ListPrice AS VARCHAR(12)) AS ListPrice
FROM dbo.DimProduct
WHERE ListPrice BETWEEN 350.00 AND 400.00;
結果集如下所示。
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
D. 使用 CAST 產生更易讀的文字
此範例會使用 SELECT 清單中的 CAST，將 Name 資料行轉換成 Char(10) 資料行。 其會使用 AdventureWorksDW2022 資料庫。
SELECT DISTINCT CAST(EnglishProductName AS CHAR(10)) AS Name,
ListPrice
FROM dbo.DimProduct
WHERE EnglishProductName LIKE 'Long-Sleeve Logo Jersey, M';
GO
結果集如下所示。
Name ListPrice
---------- ---------
Long-Sleev 31.2437
Long-Sleev 32.4935
Long-Sleev 49.99
E. 搭配 LIKE 子句使用 CAST
此範例會先將 money 資料行 SalesYTD 值轉換成資料類型 int，接著轉換成資料類型 Char(20)，讓 LIKE 子句可加以使用。
USE AdventureWorks2022;
GO
SELECT p.FirstName,
p.LastName,
s.SalesYTD,
s.BusinessEntityID
FROM Person.Person AS p
INNER JOIN Sales.SalesPerson AS s
ON p.BusinessEntityID = s.BusinessEntityID
WHERE CAST(CAST(s.SalesYTD AS INT) AS CHAR(20)) LIKE '2%';
GO
結果集如下所示。
FirstName LastName SalesYTD BusinessEntityID
---------------- ------------------- ---------------- -------------
Tsvi Reiter 2811012.7151 279
Syed Abbas 219088.8836 288
Rachel Valdez 2241204.0424 289
(3 row(s) affected)
F. 搭配具類型的 XML 使用 CONVERT 或 CAST
這些範例示範如何使用 XML 資料類型和資料行 (SQL Server)，利用 CONVERT 將資料轉換成具類型的 XML。
此範例會將含有空白字元、文字和標記的字串轉換成 XML 類型，而且會移除所有無意義的空白字元 (節點之間的界限空白字元)：
SELECT CONVERT(XML, '<root><child/></root>')
這個範例會將含有空格、文字和標記的類似字串轉換成 XML 類型，而且會保留所有無意義的空白字元 (節點之間的界限空白字元)：
SELECT CONVERT(XML, '<root> <child/> </root>', 1)
這個範例會將含有空白字元、文字和標記的字串轉換成 XML 類型：
SELECT CAST('<Name><FName>Carol</FName><LName>Elliot</LName></Name>' AS XML)
如需其他範例，請參閱建立 XML 資料的執行個體。
G. 搭配 datetime 資料使用 CAST 和 CONVERT
從 GETDATE() 值開始，此範例會顯示目前的日期和時間、使用 CAST 將目前的日期和時間變更成字元資料類型，然後使用 CONVERT 來以 ISO 8601 格式顯示日期和時間。
SELECT GETDATE() AS UnconvertedDateTime,
CAST(GETDATE() AS NVARCHAR(30)) AS UsingCast,
CONVERT(NVARCHAR(30), GETDATE(), 126) AS UsingConvertTo_ISO8601;
GO
結果集如下所示。
UnconvertedDateTime UsingCast UsingConvertTo_ISO8601
----------------------- ---------------------- ------------------------------
2022-04-18 09:58:04.570 Apr 18 2022 9:58AM 2022-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)
H. 搭配二進位和字元資料使用 CONVERT
這些範例示範如何使用不同的樣式轉換二進位和字元資料的結果。
--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)
此範例示範樣式 1 可以強制截斷結果。 結果集中的字元 0 x 會強制截斷。
SELECT CONVERT(CHAR(8), 0x4E616d65, 1) AS [Style 1, binary to character];
結果集如下所示。
Style 1, binary to character
------------------------------
0x4E616D
(1 row(s) affected)
此範例示範樣式 2 不會截斷結果，因為結果未包含字元 0x。
SELECT CONVERT(CHAR(8), 0x4E616d65, 2) AS [Style 2, binary to character];
結果集如下所示。
Style 2, binary to character
------------------------------
4E616D65
(1 row(s) affected)
將字元值 'Name' 轉換成二進位值。
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)
I. 轉換 date 和 time 資料類型
此範例示範 date、time 和 datetime 資料類型的轉換。
DECLARE @d1 DATE,
@t1 TIME,
@dt1 DATETIME;
SET @d1 = GETDATE();
SET @t1 = GETDATE();
SET @dt1 = GETDATE();
SET @d1 = GETDATE();
-- When converting date to datetime the minutes portion becomes zero.
SELECT @d1 AS [DATE],
CAST(@d1 AS DATETIME) AS [date as datetime];
-- When converting time to datetime the date portion becomes zero
-- which converts to January 1, 1900.
SELECT @t1 AS [TIME],
CAST(@t1 AS DATETIME) AS [time as datetime];
-- When converting datetime to date or time non-applicable portion is dropped.
SELECT @dt1 AS [DATETIME],
CAST(@dt1 AS DATE) AS [datetime as date],
CAST(@dt1 AS TIME) AS [datetime as time];
考慮從 date 轉換為 datetime或 datetime2 時，請確認值位於相容的範圍內。 datetime 的最小年份值為 1753，而 date 和 datetime2 的最小年份值為 0001。
DECLARE @d1 DATE, @dt1 DATETIME , @dt2 DATETIME2
SET @d1 = '1492-08-03'
--This is okay; Minimum YYYY for DATE is 0001
SET @dt2 = CAST(@d1 AS DATETIME2)
--This is okay; Minimum YYYY for DATETIME2 IS 0001
SET @dt1 = CAST(@d1 AS DATETIME)
--This will error with (Msg 242) "The conversion of a date data type to a datetime data type resulted in an out-of-range value."
--Minimum YYYY for DATETIME is 1753
J. 搭配不同格式的 datetime 資料使用 CONVERT
從 GETDATE() 值開始，此範例會使用 CONVERT 來顯示本文＜日期和時間樣式＞一節中的所有日期和時間樣式。
| 格式 # | 範例查詢 | 範例結果 |
|---|---|---|
| 0 | SELECT CONVERT(NVARCHAR, GETDATE(), 0) |
Aug 23 2019 1:39PM |
| 1 | SELECT CONVERT(NVARCHAR, GETDATE(), 1) |
08/23/19 |
| 2 | SELECT CONVERT(NVARCHAR, GETDATE(), 2) |
19.08.23 |
| 3 | SELECT CONVERT(NVARCHAR, GETDATE(), 3) |
23/08/19 |
| 4 | SELECT CONVERT(NVARCHAR, GETDATE(), 4) |
23.08.19 |
| 5 | SELECT CONVERT(NVARCHAR, GETDATE(), 5) |
23-08-19 |
| 6 | SELECT CONVERT(NVARCHAR, GETDATE(), 6) |
23 Aug 19 |
| 7 | SELECT CONVERT(NVARCHAR, GETDATE(), 7) |
Aug 23, 19 |
| 8 或 24 或108 | SELECT CONVERT(NVARCHAR, GETDATE(), 8) |
13:39:17 |
| 9 或 109 | SELECT CONVERT(NVARCHAR, GETDATE(), 9) |
Aug 23 2019 1:39:17:090PM |
| 10 | SELECT CONVERT(NVARCHAR, GETDATE(), 10) |
08-23-19 |
| 11 | SELECT CONVERT(NVARCHAR, GETDATE(), 11) |
19/08/23 |
| 12 | SELECT CONVERT(NVARCHAR, GETDATE(), 12) |
190823 |
| 13 或 113 | SELECT CONVERT(NVARCHAR, GETDATE(), 13) |
2019 年 8 月 23 日 13:39:17:090 |
| 14 或 114 | SELECT CONVERT(NVARCHAR, GETDATE(), 14) |
13:39:17:090 |
| 20 或 120 | SELECT CONVERT(NVARCHAR, GETDATE(), 20) |
2019-08-23 13:39:17 |
| 21 或 25 或 121 | SELECT CONVERT(NVARCHAR, GETDATE(), 21) |
2019-08-23 13:39:17.090 |
| 22 | SELECT CONVERT(NVARCHAR, GETDATE(), 22) |
08/23/19 1:39:17 PM |
| 23 | SELECT CONVERT(NVARCHAR, GETDATE(), 23) |
2019-08-23 |
| 101 | SELECT CONVERT(NVARCHAR, GETDATE(), 101) |
08/23/2019 |
| 102 | SELECT CONVERT(NVARCHAR, GETDATE(), 102) |
2019.08.23 |
| 103 | SELECT CONVERT(NVARCHAR, GETDATE(), 103) |
23/08/2019 |
| 104 | SELECT CONVERT(NVARCHAR, GETDATE(), 104) |
23.08.2019 |
| 105 | SELECT CONVERT(NVARCHAR, GETDATE(), 105) |
23-08-2019 |
| 106 | SELECT CONVERT(NVARCHAR, GETDATE(), 106) |
23 Aug 2019 |
| 107 | SELECT CONVERT(NVARCHAR, GETDATE(), 107) |
Aug 23, 2019 |
| 110 | SELECT CONVERT(NVARCHAR, GETDATE(), 110) |
08-23-2019 |
| 111 | SELECT CONVERT(NVARCHAR, GETDATE(), 111) |
2019/08/23 |
| 112 | SELECT CONVERT(NVARCHAR, GETDATE(), 112) |
20190823 |
| 113 | SELECT CONVERT(NVARCHAR, GETDATE(), 113) |
23 Aug 2019 13:39:17.090 |
| 120 | SELECT CONVERT(NVARCHAR, GETDATE(), 120) |
2019-08-23 13:39:17 |
| 121 | SELECT CONVERT(NVARCHAR, GETDATE(), 121) |
2019-08-23 13:39:17.090 |
| 126 | SELECT CONVERT(NVARCHAR, GETDATE(), 126) |
2019-08-23T13:39:17.09 |
| 127 | SELECT CONVERT(NVARCHAR, GETDATE(), 127) |
2019-08-23T13:39:17.09 |
| 130 | SELECT CONVERT(NVARCHAR, GETDATE(), 130) |
22 ذو الحجة 1440 1:39:17.090P |
| 131 | SELECT CONVERT(NVARCHAR, GETDATE(), 131) |
22/12/1440 1:39:17.090PM |
K. 經允許轉換中的資料類型優先順序效果
下列範例會定義 varchar(10) 類型的變數、將整數值指派給變數，然後使用字串選取變數的串連。
DECLARE @string VARCHAR(10);
SET @string = 1;
SELECT @string + ' is a string.' AS Result
結果集如下所示。
Result
-----------------------
1 is a string.
int 值 1 已轉換成 varchar。
這個範例會顯示類似的查詢，但改為使用 int 變數：
DECLARE @notastring INT;
SET @notastring = '1';
SELECT @notastring + ' is not a string.' AS Result
在此情況下，SELECT 陳述式會擲回下列錯誤：
Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value ' is not a string.' to data type int.
為了評估運算式 @notastring + ' is not a string.'，SQL Server 會遵循資料類型優先順序規則，在計算運算式的結果前完成隱含轉換。 因為 int 的優先順序高於 varchar，所以 SQL Server 會嘗試將字串轉換成整數；但由於此字串無法轉換成整數，因此會失敗。
若提供可進行轉換的字串，則陳述式會成功，如下列範例所示：
DECLARE @notastring INT;
SET @notastring = '1';
SELECT @notastring + '1'
在此情況下，字串 '1' 可轉換成整數值 1，所以此 SELECT 陳述式將會傳回值 2。 在提供的資料類型為整數時，+ 運算子會變成加法數學運算子，而非字串串連。
範例：Azure Synapse Analytics 和 Analytics Platform System (PDW)
L. 使用 CAST 和 CONVERT
此範例會擷取定價第一位數是 3 的產品名稱，而且會將這些產品的 ListPrice 轉換成 int。其會使用 AdventureWorksDW2022 資料庫。
SELECT EnglishProductName AS ProductName, ListPrice
FROM dbo.DimProduct
WHERE CAST(ListPrice AS int) LIKE '3%';
此範例會示範相同的查詢，但使用的是 CONVERT 而非 CAST。 其會使用 AdventureWorksDW2022 資料庫。
SELECT EnglishProductName AS ProductName, ListPrice
FROM dbo.DimProduct
WHERE CONVERT(INT, ListPrice) LIKE '3%';
M. 搭配算術運算子使用 CAST
此範例會將產品單價 (UnitPrice) 除以折扣百分比 (UnitPriceDiscountPct) 來計算單一資料行值。 此結果接著會四捨五入到最接近的整數，最後轉換成 int 資料類型。 這個範例會使用 AdventureWorksDW2022 資料庫。
SELECT ProductKey, UnitPrice,UnitPriceDiscountPct,
CAST(ROUND (UnitPrice*UnitPriceDiscountPct,0) AS int) AS DiscountPrice
FROM dbo.FactResellerSales
WHERE SalesOrderNumber = 'SO47355'
AND UnitPriceDiscountPct > .02;
結果集如下所示。
ProductKey UnitPrice UnitPriceDiscountPct DiscountPrice
---------- --------- -------------------- -------------
323 430.6445 0.05 22
213 18.5043 0.05 1
456 37.4950 0.10 4
456 37.4950 0.10 4
216 18.5043 0.05 1
N. 搭配 LIKE 子句使用 CAST
此範例會先將 money 資料行 ListPrice 轉換成 int 類型，接著轉換成 char(20) 類型，讓 LIKE 子句可以使用它。 這個範例會使用 AdventureWorksDW2022 資料庫。
SELECT EnglishProductName AS Name, ListPrice
FROM dbo.DimProduct
WHERE CAST(CAST(ListPrice AS INT) AS CHAR(20)) LIKE '2%';
O. 搭配 datetime 資料使用 CAST 和 CONVERT
此範例會顯示目前的日期和時間，並使用 CAST 將目前的日期和時間變更成字元資料類型，最後使用 CONVERT 以 ISO 8601 格式顯示日期和時間。 這個範例會使用 AdventureWorksDW2022 資料庫。
SELECT TOP(1)
SYSDATETIME() AS UnconvertedDateTime,
CAST(SYSDATETIME() AS NVARCHAR(30)) AS UsingCast,
CONVERT(NVARCHAR(30), SYSDATETIME(), 126) AS UsingConvertTo_ISO8601
FROM dbo.DimCustomer;
結果集如下所示。
UnconvertedDateTime UsingCast UsingConvertTo_ISO8601
--------------------- --------------------------- ---------------------------
07/20/2010 1:44:31 PM 2010-07-20 13:44:31.5879025 2010-07-20T13:44:31.5879025
此範例是上一個範例的相反操作。 此範例會將日期和時間顯示成字元資料，並使用 CAST 將字元資料變更為 datetime 資料類型，然後使用 CONVERT 將字元資料變更為 datetime 資料類型。 這個範例會使用 AdventureWorksDW2022 資料庫。
SELECT TOP(1)
'2010-07-25T13:50:38.544' AS UnconvertedText,
CAST('2010-07-25T13:50:38.544' AS DATETIME) AS UsingCast,
CONVERT(DATETIME, '2010-07-25T13:50:38.544', 126) AS UsingConvertFrom_ISO8601
FROM dbo.DimCustomer;
結果集如下所示。
UnconvertedText UsingCast UsingConvertFrom_ISO8601
----------------------- ----------------------- ------------------------
2010-07-25T13:50:38.544 07/25/2010 1:50:38 PM 07/25/2010 1:50:38 PM