資料類型轉換 (資料庫引擎)Data type conversion (Database Engine)

適用於: 是SQL Server 是Azure SQL Database 是Azure SQL 資料倉儲 是平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

在下列情況中可以轉換資料類型:Data types can be converted in the following scenarios:

  • 將一個物件的資料移到另一個物件、與另一個物件的資料作比較,或與另一個物件的資料結合時,可能需要將資料從一個物件的資料類型轉換成其他物件的資料類型。When data from one object is moved to, compared with, or combined with data from another object, the data may have to be converted from the data type of one object to the data type of the other.
  • Transact-SQLTransact-SQL 結果資料行、傳回碼或輸出參數的資料移至程式變數時,該資料必須從 SQL ServerSQL Server 系統資料類型,轉換成變數的資料類型。When data from a Transact-SQLTransact-SQL result column, return code, or output parameter is moved into a program variable, the data must be converted from the SQL ServerSQL Server system data type to the data type of the variable.

在應用程式變數與 SQL ServerSQL Server 結果集資料行、傳回程式碼、參數或參數標記之間轉換時,所支援的資料類型轉換是由資料庫 API 定義。When you convert between an application variable and a SQL ServerSQL Server result set column, return code, parameter, or parameter marker, the supported data type conversions are defined by the database API.

隱含及明確轉換Implicit and explicit conversion

資料類型可以隱含或明確地轉換。Data types can be converted either implicitly or explicitly.

使用者看不到隱含轉換。Implicit conversions are not visible to the user. SQL ServerSQL Server 會自動將資料從一種類型轉換成其他資料類型。automatically converts the data from one data type to another. 例如,如果將 smallintint做比較,會先將 smallint 隱含轉換成 int再繼續比較。For example, when a smallint is compared to an int, the smallint is implicitly converted to int before the comparison proceeds.

GETDATE() 會隱含轉換成日期樣式 0。GETDATE() implicitly converts to date style 0. SYSDATETIME() 會隱含轉換成日期樣式 21。SYSDATETIME() implicitly converts to date style 21.

明確轉換使用 CAST 或 CONVERT 函數。Explicit conversions use the CAST or CONVERT functions.

CAST 和 CONVERT 函數會將數值 (本機變數、資料行或其他運算式) 轉換成另一個資料類型。The CAST and CONVERT functions convert a value (a local variable, a column, or another expression) from one data type to another. 例如,下列 CAST 函數會將 $157.27 的數值轉換成 '157.27' 的字元字串:For example, the following CAST function converts the numeric value of $157.27 into a character string of '157.27':

CAST ( $157.27 AS VARCHAR(10) )  

如果您希望 Transact-SQLTransact-SQL 程式碼符合 ISO,請使用 CAST 來取代 CONVERT。Use CAST instead of CONVERT if you want Transact-SQLTransact-SQL program code to comply with ISO. 要善用 CONVERT 的樣式功能,可不使用 CAST 而改用 CONVERT。Use CONVERT instead of CAST to take advantage of the style functionality in CONVERT.

下圖顯示 SQL ServerSQL Server 系統提供之資料類型所能使用的所有明確與隱含資料類型轉換。The following illustration shows all explicit and implicit data type conversions that are allowed for SQL ServerSQL Server system-supplied data types. 這些包括 xmlbigintsql_variantThese include xml, bigint, and sql_variant. sql_variant 資料類型進行指派時,不可使用隱含轉換,但可以隱含轉換成 sql_variantThere is no implicit conversion on assignment from the sql_variant data type, but there is implicit conversion to sql_variant.

資料類型轉換表Data type conversion table

資料類型轉換行為Data type conversion behaviors

當您要將某 SQL ServerSQL Server 物件的資料類型轉換成其他資料類型時,有部分隱含與明確資料類型的轉換不受支援。Some implicit and explicit data type conversions are not supported when you are converting the data type of one SQL ServerSQL Server object to another. 例如,nchar 值無法轉換成 image 值。For example, an nchar value cannot be converted to an image value. 您只可使用明確轉換將 nchar 轉換成 binary;系統並不支援隱含轉換成 binaryAn nchar can only be converted to binary by using explicit conversion, an implicit conversion to binary is not supported. nchar 可以明確或隱含轉換成 nvarcharHowever, an nchar can be explicitly or implicitly converted to nvarchar.

下列主題說明其對應資料類型所表現的轉換行為:The following topics describe the conversion behaviors exhibited by their corresponding data types:

使用 OLE Automation 預存程序轉換資料類型Converting Data Types by Using OLE Automation Stored Procedures

因為 SQL ServerSQL Server 使用 Transact-SQLTransact-SQL 資料類型,而 OLE Automation 使用 Visual BasicVisual Basic 資料類型,所以 OLE Automation 預存程序必須轉換在兩者之間傳遞的資料。Because SQL ServerSQL Server uses Transact-SQLTransact-SQL data types and OLE Automation uses Visual BasicVisual Basic data types, the OLE Automation stored procedures must convert the data that passes between them.

下表說明從 SQL ServerSQL ServerVisual BasicVisual Basic 的資料類型轉換。The following table describes SQL ServerSQL Server to Visual BasicVisual Basic data type conversions.

SQL Server 資料類型SQL Server data type Visual Basic 資料類型Visual Basic data type
charvarchartextnvarcharntextchar, varchar, text, nvarchar, ntext StringString
decimalnumericdecimal, numeric StringString
bitbit 布林Boolean
binaryvarbinaryimagebinary, varbinary, image 一維 Byte() 陣列One-dimensional Byte() array
intint LongLong
smallintsmallint IntegerInteger
tinyinttinyint 位元組Byte
floatfloat DoubleDouble
realreal SingleSingle
moneysmallmoneymoney, smallmoney 貨幣Currency
datetimesmalldatetimedatetime, smalldatetime 日期Date
設成 NULL 的任何類型Anything set to NULL Variant 設為 NullVariant set to Null

所有單一的 SQL ServerSQL Server 值皆會轉換成單一的 Visual BasicVisual Basic 值,但不包括 binaryvarbinaryimage 值。All single SQL ServerSQL Server values are converted to a single Visual BasicVisual Basic value with the exception of binary, varbinary, and image values. 這些值會在 Visual BasicVisual Basic 中轉換成一維的 Byte() 陣列。These values are converted to a one-dimensional Byte() array in Visual BasicVisual Basic. 此陣列的範圍為 Byte ( 0 到 length 1 ),其中 lengthSQL ServerSQL Server binaryvarbinaryimage 值中的位元組數目。This array has a range of Byte( 0 to length 1 ) where length is the number of bytes in the SQL ServerSQL Server binary, varbinary, or image values.

這些轉換是從 Visual BasicVisual Basic 資料類型到 SQL ServerSQL Server 資料類型。These are the conversions from Visual BasicVisual Basic data types to SQL ServerSQL Server data types.

Visual Basic 資料類型Visual Basic data type SQL Server 資料類型SQL Server data type
LongIntegerByteBooleanObjectLong, Integer, Byte, Boolean, Object intint
DoubleSingleDouble, Single floatfloat
貨幣Currency moneymoney
日期Date datetimedatetime
字元數不超過 4000 個的 StringString with 4000 characters or less varchar/nvarcharvarchar/nvarchar
多於 4000 個字元的 StringString with more than 4000 characters text/ntexttext/ntext
位元組數不超過 8000 的一維 Byte() 陣列One-dimensional Byte() array with 8000 bytes or less varbinaryvarbinary
位元組數超過 8000 的一維 Byte() 陣列One-dimensional Byte() array with more than 8000 bytes imageimage

另請參閱See also

OLE Automation 預存程序 (Transact-SQL)OLE Automation Stored Procedures (Transact-SQL)
CAST 和 CONVERT (Transact-SQL)CAST and CONVERT (Transact-SQL)
資料類型 (Transact-SQL)Data Types (Transact-SQL)
COLLATE (Transact-SQL)COLLATE (Transact-SQL)