Implicit conversions are those conversions that occur without specifying either the CAST or CONVERT function. Explicit conversions are those conversions that require the CAST or CONVERT function to be specified. The following illustration shows all explicit and implicit data type conversions that are allowed for SQL Server 2005 system-supplied data types. These include
xml,
bigint, and
sql_variant. There is no implicit conversion on assignment from the
sql_variant data type, but there is implicit conversion to
sql_variant.
 Note: |
Because Unicode data always uses an even number of bytes, use caution when you convert binary or varbinary to or from Unicode supported data types. For example, the following conversion does not return a hexadecimal value of 41; it returns 4100: SELECT CAST(CAST(0x41 AS nvarchar) AS varbinary). |
Large-value Data Types
Large-value data types exhibit the same implicit and explicit conversion behavior as their smaller counterparts, specifically the varchar, nvarchar and varbinary data types. However, you should consider the following guidelines:
- Conversion from image to varbinary(max) and vice-versa is an implicit conversion, and so are conversions between text and varchar(max), and ntext and nvarchar(max).
- Conversion from large-value data types, such as varchar(max), to a smaller counterpart data type, such as varchar, is an implicit conversion, but truncation will occur if the large value is too big for the specified length of the smaller data type.
- Conversion from varchar, nvarchar, or varbinary to their corresponding large-value data types is performed implicitly.
- Conversion from the sql_variant data type to the large-value data types is an explicit conversion.
- Large-value data types cannot be converted to the sql_variant data type.
For information about converting Microsoft .NET Framework common language runtime (CLR) user-defined types, see Performing Operations on User-defined Types. For more information about converting from the xml data type, see Generating XML Instances.
xml Data Type
text and image Data Types
Automatic data type conversion is not supported for the text and image data types. You can explicitly convert text data to character data, and image data to binary or varbinary, but the maximum length is 8000 bytes. If you try an incorrect conversion such as trying to convert a character expression that includes letters to an int, SQL Server returns an error message.
Output Collation
When the output of CAST or CONVERT is a character string, and the input is a character string, the output has the same collation and collation label as the input. If the input is not a character string, the output has the default collation of the database, and a collation label of coercible-default. For more information, see Collation Precedence (Transact-SQL).
To assign a different collation to the output, apply the COLLATE clause to the result expression of the CAST or CONVERT function. For example:
SELECT CAST('abc' AS varchar(5)) COLLATE French_CS_AS
Truncating and Rounding Results
When you convert character or binary expressions (char, nchar, nvarchar, varchar, binary, or varbinary) to an expression of a different data type, data can be truncated, only partially displayed, or an error is returned because the result is too short to display. Conversions to char, varchar, nchar, nvarchar, binary, and varbinary are truncated, except for the conversions shown in the following table.
| From data type |
To data type |
Result |
| int, smallint, or tinyint |
char |
* |
|
varchar |
* |
|
nchar |
E |
|
nvarchar |
E |
| money, smallmoney, numeric, decimal, float, or real |
char |
E |
|
varchar |
E |
|
nchar |
E |
|
nvarchar |
E |
* = Result length too short to display. E = Error returned because result length is too short to display.
SQL Server guarantees that only roundtrip conversions, conversions that convert a data type from its original data type and back again, will yield the same values from version to version. The following example shows such a roundtrip conversion:
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))
| Note: |
| Do not try to construct binary values and then convert them to a data type of the numeric data type category. SQL Server does not guarantee that the result of a decimal or numeric data type conversion to binary will be the same between versions of SQL Server. |
The following example shows a resulting expression that is too small to display.
USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName, SUBSTRING(c.Title, 1, 25) AS Title, CAST(e.SickLeaveHours AS char(1)) AS 'Sick Leave'
FROM HumanResources.Employee e JOIN Person.Contact c ON e.EmployeeID = c. ContactID
WHERE NOT EmployeeID >5
Here is the result set.
FirstName LastName Title Sick Leave
--------- --------- ------------------- -----------
Gustavo Achong Mr. *
Catherine Abel Ms. *
Kim Abercrombie Ms. *
Humberto Acevedo Sr. *
Pilar Ackerman Sra. *
(5 row(s) affected)
When you convert data types that differ in decimal places, sometimes the result value is truncated and at other times it is rounded. The following table shows the behavior.
| From |
To |
Behavior |
| numeric |
numeric |
Round |
| numeric |
int |
Truncate |
| numeric |
money |
Round |
| money |
int |
Round |
| money |
numeric |
Round |
| float |
int |
Truncate |
| float |
numeric |
Round |
| float |
datetime |
Round |
| datetime |
int |
Round |
For example, the result of the following conversion is 10:
SELECT CAST(10.6496 AS int)
When you convert data types in which the target data type has fewer decimal places than the source data type, the value is rounded. For example, the result of the following conversion is $10.3497:
SELECT CAST(10.3496847 AS money)
SQL Server returns an error message when nonnumeric char, nchar, varchar, or nvarchar data is converted to int, float, numeric, or decimal. SQL Server also returns an error when an empty string (” “) is converted to numeric or decimal.
Converting Binary String Data
When binary or varbinary data is converted to character data and an odd number of values is specified following the x, SQL Server adds a 0 (zero) after the x to make an even number of values.
Binary data is made up of the characters from 0 through 9 and from A through F, or from a through f, in groups of two characters each. Binary strings must be preceded by 0x. For example, to input FF, type 0xFF. The maximum value is a binary value of 8000 bytes, each of which is FF. The binary data types are not for hexadecimal data but instead for bit patterns. Conversions and calculations of hexadecimal numbers stored as binary data can be unreliable.
When you specify the length of a binary data type, every two characters count as one. A length of 10 signifies that 10 two-character groupings will be entered.
Empty binary strings are represented by 0x and can be stored as binary data.
Syntax
