資料類型轉換 (Database Engine)

發行項
12/15/2008

下列情況中可以轉換資料類型：

將一個物件的資料移到、比較或結合另一個物件的資料時，可能要將資料從一個物件的資料類型轉換成其他物件的資料類型。
當 Transact-SQL 結果資料行的資料、傳回程式碼或輸出參數移入程式變數時，資料必須從 SQL Server 2005 系統資料類型轉換成變數的資料類型。

資料類型可以隱含或明確地轉換：

使用者看不到隱含轉換。
SQL Server 會自動將資料轉換成另一種資料類型。例如，如果將 smallint 與 int 做比較，就會先將 smallint 隱含地轉換成 int，然後再進行比較。
明確轉換使用 CAST 或 CONVERT 函數。
CAST 與 CONVERT 函數會將數值 (本機變數、資料行或其他運算式) 轉換成另一個資料類型。例如，下列 CAST 函數會將 $157.27 的數值轉換成 '157.27' 的字元字串：
```
CAST ( $157.27 AS VARCHAR(10) )
```
如果希望 Transact-SQL 程式碼遵守 SQL-92，請使用 CAST 而不要用 CONVERT。使用 CONVERT 而不用 CAST 是為了獲得 CONVERT 中的樣式功能。

當您要將一個 SQL Server 物件的資料類型轉換到另一個，不支援部份隱含與明確資料類型的轉換。例如，nchar 值無法轉換成 image 值。您只能使用明確轉換將 nchar 轉換成 binary；不支援隱含轉換成 binary。但是，nchar 可以明確或隱含轉換成 nvarchar。

處理 sql_variant 資料類型時，SQL Server 支援將有其他資料類型的物件隱含轉換成 sql_variant 類型。但是 SQL Server 不支援從 sql_variant 資料隱含轉換成其他資料類型的物件。

如需有關 SQL Server 物件之間支援的轉換的詳細資訊，請參閱＜CAST 和 CONVERT (Transact-SQL)＞。

在應用程式變數與 SQL Server 結果集資料行、傳回程式碼、參數或參數標記之間轉換時，支援的資料類型轉換是由資料庫 API 定義。如需詳細資訊，請參閱＜將資料移到程式變數＞。

資料類型轉換行為

本主題中的下列各節描述下列資料類型所顯示的轉換行為：

binary 和 varbinary 資料	float 和 real 資料
bit 資料	money 資料
Character 資料	decimal 和 numeric 資料
datetime 和 smalldatetime 資料	使用 OLE Automation 預存程序的資料類型

轉換 binary 與 varbinary 資料

當資料從字串資料類型 (char、varchar、nchar、nvarchar、binary、varbinary、text、ntext 或 image) 轉換成不等長的 binary 或 varbinary 資料類型時，SQL Server 會填補或截斷右邊的資料。將其他資料類型轉換成 binary 或 varbinary 時，則在左邊填補或截斷資料。使用十六進位零進行填補。

如果 binary 資料是搬移資料最簡易的方式，將資料轉換成為 binary 和 varbinary 資料類型會非常有幫助。將各種資料類型的值轉換為有足夠大小的二進位值，然後再將值轉換回原來的資料類型，如果兩個轉換都在相同版本的 SQL Server 上進行，那麼將會得到相同的值。數值的二進位表示法可能會隨著不同的 SQL Server 版本而變更。

您可以將 int、smallint 和 tinyint 轉換成 binary 或 varbinary，但如果您將 binary 值轉換回整數值，若有發生截斷，則此值會與原始的整數值不同。例如，下列 SELECT 陳述式將通常顯示為 0x0001e240 的二進位顯示成整數值 123456：

SELECT CAST( 123456 AS BINARY(4) )

但是，下列 SELECT 陳述式顯示如果 binary 目標太小，放不下整個數值，就會悄悄地將前面的位數截斷，所以同一個數字會儲存為 0xe240：

SELECT CAST( 123456 AS BINARY(2) )

下列批次顯示悄悄截斷可能在不產生錯誤的情況下影響算術運算：

DECLARE @BinaryVariable2 BINARY(2)

SET @BinaryVariable2 = 123456
SET @BinaryVariable2 = @BinaryVariable2 + 1

SELECT CAST( @BinaryVariable2 AS INT)
GO

最後的結果是 57921，不是 123457。

附註：
在 SQL Server 版本之間轉換任何資料類型與 binary 資料類型，不保證結果都會一樣。

轉換成 bit 資料

轉換成 bit 會將任何非零的值升級成 1。

轉換字元資料

將字元運算式轉換成不同大小的字元資料類型時，對新資料類型而言太大的值會被截斷。

當字元運算式被轉換成不同資料類型或大小的字元運算式時，例如從 char(5) 轉換成 varchar(5)，或從 char(20) 轉換成 char(15)，輸入數值的定序會被指派轉換的數值。如果將非字元運算式轉換成字元資料類型，會將目前資料庫的預設定序指派給轉換的數值。不論哪一種狀況，都可以使用 COLLATE 子句指派特定的定序。

附註：
支援 char 和 varchar 資料類型的字碼頁轉換，但不支援 text 資料類型的字碼頁轉換。舊版的 SQL Server，不會報告字碼頁翻譯期間的資料遺失。

轉換成近似 numeric 資料類型的字元運算式可以包含選擇性的指數標記法 (小寫 e 或大寫 E 後面跟著選擇性的加號 (+) 或減號 (-)，再接著數字)。

轉換成正確 numeric 資料類型的字元運算式必須由數字、小數點和選擇性的加號 (+) 或減號 (-) 組成。前置的空白會被忽略。字串不能用逗號分隔符號 (如 123,456.00 中的千位分隔符號)。

轉換成 money 或 smallmoney 資料類型的字元運算式也可以包含選擇性的小數點和錢幣符號 ($)。可用逗號分隔符號 (如 $123,456.00)。

下列範例示範如何轉換資料以供顯示。此範例會將銷售資料轉換成字元資料，然後執行字串比較，並將今天的日期轉換成樣式 3：dd/mm/yy。

USE AdventureWorks;
GO
SELECT SalesPersonID,
   CAST(SalesYTD AS varchar(12)),
   CONVERT(VARCHAR(12), GETDATE(), 3)
FROM Sales.SalesPerson
WHERE CAST(SalesYTD AS varchar(20) ) LIKE '1%'
GO

此範例將 uniqueidentifier 值轉換成 char 資料類型。

DECLARE @myid uniqueidentifier
SET @myid = NEWID()
SELECT CONVERT(char(255), @myid) AS 'char'
GO

此範例將今天的日期轉換成樣式 3：dd/mm/yy。

SELECT CONVERT(char(12), GETDATE(), 3)
GO

轉換 datetime 與 smalldatetime 資料

當您轉換成 datetime 時，SQL Server 2005 會拒絕所有無法辨識成日期的值。這些包括比西元 1 年 1 月 1 日 (January 1, 1A.D) 還早的日期。刀日期是在正確的範圍 (西元 1900 年 1 月 1 日到 2079 年 6 月 6 日) 時，您可以將 datetime 值轉換成 smalldatetime。時間值則是四捨五入到最接近的分鐘。

下列範例分別將 smalldatetime 和 datetime 值轉換成 varchar 和 binary 資料類型。

DECLARE @mydate_sm smalldatetime
SET @mydate_sm = '4/05/98'

SELECT CAST(@mydate_sm AS varchar) AS SM_DATE_VARCHAR
GO

DECLARE @mydate  datetime
SET @mydate     = '4/05/98'

SELECT  CAST(@mydate AS binary) AS DATE_BINARY
GO

以下為結果集：

(1 row(s) affected)

SM_DATE_VARCHAR                
------------------------------ 
Apr  5 1998 12:00AM            

(1 row(s) affected)

DATE_BINARY                                                    
-------------------------------------------------------------- 
0x0000000000000000000000000000000000000000000000008c3000000000 

(1 row(s) affected)

轉換 float 與 real 資料

將 float 值轉換成任何整數類型時會被截斷。

當您想要從 float 或 real 轉換成字元資料時，使用 STR 字串函數比 CAST( ) 來得有用。這是因為 STR 可以對格式有較多的控制。如需詳細資訊，請參閱＜STR (Transact-SQL)＞和＜函數 (Transact-SQL)＞。

轉換 money 資料

當您從任何整數資料類型轉換成 money 時，會假設單位是貨幣單位。例如，整數值 4 轉換成 money 時相當於 4 個貨幣單位。

下列範例分別將 smallmoney 和 money 值轉換成 varchar 和 decimal 資料類型。

USE AdventureWorks;
GO
DECLARE @mymoney_sm smallmoney;
SET  @mymoney_sm = 3148.29;
SELECT  CAST(@mymoney_sm AS varchar) AS 'SM_MONEY varchar';
GO
DECLARE @mymoney    money;
SET  @mymoney    = 3148.29;
SELECT  CAST(@mymoney AS decimal)    AS 'MONEY DECIMAL';

GO

以下為結果集：

SM_MONEY VARCHAR               
------------------------------ 
3148.29                        

(1 row(s) affected)

MONEY DECIMAL          
---------------------- 
3148                   

(1 row(s) affected)

轉換 decimal 與 numeric 資料

對於 decimal 和 numeric 資料類型，SQL Server 會將每個有效位數和小數位數的特定結合視為不同的資料類型。例如，decimal(5,5) 和 decimal(5,0) 是視為不同的資料類型。

在 Transact-SQL 陳述式中，會使用必要的最小有效位數與小數位數，自動將有小數點的常數轉換成 numeric 資料值。例如，常數 12.345 會轉換成有效位數 5、小數位數 3 的 numeric 值。

從 decimal 或 numeric 轉換到 float 或 real 可能會流失有效位數。而從 int、smallint、tinyint、float、real、money 或 smallmoney 轉換成 decimal 或 numeric 可能會造成溢位。

依預設，SQL Server 在將數字轉換成有效位數與小數位數較小的 decimal 或 numeric 值時會使用四捨五入。但是，如果 SET ARITHABORT 選項是 ON，SQL Server 會在發生溢位時產生錯誤。只是流失有效位數與小數位數還不足以產生錯誤。

使用 OLE Automation 預存程序轉換資料類型

因為 SQL Server 使用 Transact-SQL 資料類型，而 OLE Automation 使用 Visual Basic 資料類型，所以 OLE Automation 預存程序必須轉換在兩者之間傳遞的資料。

下表描述 SQL Server 到 Visual Basic 的資料類型轉換。

SQL Server 資料類型	Visual Basic 資料類型
char, varchar, text, nvarchar, ntext	String
decimal, numeric	String
bit	Boolean
binary, varbinary, image	一維 Byte() 陣列
int	Long
smallint	Integer
tinyint	Byte
float	Double
real	Single
money, smallmoney	Currency
datetime, smalldatetime	Date
設成 NULL 的任何類型	設成 Null 的 Variant

所有單一的 SQL Server 值會被轉換成單一的 Visual Basic 值，除了 binary、varbinary 和 image 值以外。這些值會在 Visual Basic 中轉換成一維的 Byte() 陣列。此陣列的範圍從 Byte(0 到 length1)，其中 length 是 SQL Server binary 中的位元組數目、varbinary 或 image 值。

這些轉換是從 Visual Basic 資料類型到 SQL Server 資料類型。

Visual Basic 資料類型	SQL Server 資料類型
Long, Integer, Byte, Boolean, Object	int
Double, Single	float
Currency	money
Date	datetime
4000 或不到 4000 個字元的 String	varchar/nvarchar
大於 4000 個字元的 String	text/ntext
8000 或不到 8000 個位元組的一維 Byte() 陣列	varbinary
大於 8000 個位元組的一維 Byte() 陣列	image

請參閱

共用方式為