Data Type Conversion (Database Engine)
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.
When data from a Transact-SQL result column, return code, or output parameter is moved into a program variable, the data must be converted from the SQL Server system data type to the data type of the variable.
Data types can be converted either implicitly or explicitly:
Implicit conversions are not visible to the user.
SQL Server automatically converts the data from one data type to another. For example, when a smallint is compared to an int, the smallint is implicitly converted to int before the comparison proceeds. Note that the query optimizer may generate a query plan to perform this conversion at any time. This may cause a runtime error for conversion failures such as loss of precision and an attempt to convert a nonnumeric string to a number. For more information, see Troubleshooting Errors and Warnings on Query Expressions.
Explicit conversions use the CAST or CONVERT functions.
The CAST and CONVERT functions convert a value (a local variable, a column, or another expression) from one data type to another. 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) )Use CAST instead of CONVERT if you want Transact-SQL program code to comply with ISO. Use CONVERT instead of CAST to take advantage of the style functionality in CONVERT.
Some implicit and explicit data type conversions are not supported when you are converting the data type of one SQL Server object to another. For example, an nchar value cannot be converted to an image value. An nchar can only be converted to binary by using explicit conversion, an implicit conversion to binary is not supported. However, an nchar can be explicitly or implicitly converted to nvarchar.
When handling the sql_variant data type, SQL Server supports implicit conversions of objects with other data types to the sql_variant type. However, SQL Server does not support implicit conversions from sql_variant data to an object with another data type.
For more information about supported conversions between SQL Server objects, see CAST and CONVERT (Transact-SQL).
When you convert between an application variable and a SQL Server result set column, return code, parameter, or parameter marker, the supported data type conversions are defined by the database API. For more information, see Moving Data to Program Variables.
Data Type Conversion Behaviors
The following sections in this topic describe the conversion behaviors exhibited by the following data types:
binary and varbinary data |
money data |
bit data |
decimal and numeric data |
Character data |
Data types by using OLE Automation stored procedures |
date and time data |
integer data |
float and real data |
Converting binary and varbinary Data
When data is converted from a string data type (char, varchar, nchar, nvarchar, binary, varbinary, text, ntext, or image) to a binary or varbinary data type of unequal length, SQL Server pads or truncates the data on the right. When other data types are converted to binary or varbinary, the data is padded or truncated on the left. Padding is achieved by using hexadecimal zeros.
Converting data to the binary and varbinary data types is useful if binary data is the easiest way to move data around. Converting any value of any type to a binary value of large enough size and then back to the type, will always result in the same value if both conversions are taking place on the same version of SQL Server. The binary representation of a value might change from version to version of SQL Server.
You can convert int, smallint, and tinyint to binary or varbinary, but if you convert the binary value back to an integer value, this value will be different from the original integer value if truncation has occurred. For example, the following SELECT statement shows that the integer value 123456 is usually stored as a binary 0x0001e240:
SELECT CAST( 123456 AS BINARY(4) )
However, the following SELECT statement shows that if the binary target is too small to hold the entire value, the leading digits are silently truncated so that the same number is stored as 0xe240:
SELECT CAST( 123456 AS BINARY(2) )
The following batch shows that this silent truncation can affect arithmetic operations without raising an error:
DECLARE @BinaryVariable2 BINARY(2)
SET @BinaryVariable2 = 123456
SET @BinaryVariable2 = @BinaryVariable2 + 1
SELECT CAST( @BinaryVariable2 AS INT)
GO
The final result is 57921, not 123457.
Note
Conversions between any data type and the binary data types are not guaranteed to be the same between versions of SQL Server.
Converting to bit Data
Converting to bit promotes any nonzero value to 1.
Converting Character Data
When character expressions are converted to a character data type of a different size, values that are too long for the new data type are truncated. The uniqueidentifier type is considered a character type for the purposes of conversion from a character expression, and therefore is subject to the truncation rules for converting to a character type. For more information, see uniqueidentifier (Transact-SQL).
When a character expression is converted to a character expression of a different data type or size, such as from char(5) to varchar(5), or char(20) to char(15), the collation of the input value is assigned to the converted value. If a noncharacter expression is converted to a character data type, the default collation of the current database is assigned to the converted value. In either case, you can assign a specific collation by using the COLLATE clause.
Note
Code page translations are supported for char and varchar data types, but not for text data type. As with earlier versions of SQL Server, data loss during code page translations is not reported.
Character expressions that are being converted to an approximate numeric data type can include optional exponential notation (a lowercase e or uppercase E followed by an optional plus (+) or minus (-) sign and then a number).
Character expressions that are being converted to an exact numeric data type must consist of digits, a decimal point, and an optional plus (+) or minus (-). Leading blanks are ignored. Comma separators, such as the thousands separator in 123,456.00, are not allowed in the string.
Character expressions being converted to money or smallmoney data types can also include an optional decimal point and dollar sign ($). Comma separators, as in $123,456.00, are allowed.
The following example shows how to convert data for display. This example converts sales data to character data before performing a string comparison and converts the current date to style 3, dd/mm/yy.
USE AdventureWorks2008R2;
GO
SELECT BusinessEntityID,
CAST(SalesYTD AS varchar(12)),
CONVERT(VARCHAR(12), GETDATE(), 3)
FROM Sales.SalesPerson
WHERE CAST(SalesYTD AS varchar(20) ) LIKE '1%';
GO
This example converts a uniqueidentifier value to a char data type.
DECLARE @myid uniqueidentifier
SET @myid = NEWID()
SELECT CONVERT(char(255), @myid) AS 'char';
GO
This example converts the current date to style 3, dd/mm/yy.
SELECT CONVERT(char(12), GETDATE(), 3);
GO
Converting Date and Time Data
When you convert to date and time data types, SQL Server rejects all values it cannot recognize as dates or times. For an overview of all Transact-SQL date and time data types and functions, see Date and Time Functions (Transact-SQL).
The following example converts date and datetime2 values to varchar and binary data types, respectively.
DECLARE @mydate date;
SET @mydate = '4/05/98';
SELECT CAST(@mydate AS varchar) AS DATE_VARCHAR;
GO
DECLARE @mydate datetime2;
SET @mydate = '4/05/98';
SELECT CAST(@mydate AS binary) AS DATE_BINARY;
GO
Here is the result set.
(1 row(s) affected)
DATE_VARCHAR
------------------------------
Apr 5 1998
(1 row(s) affected)
DATE_BINARY
--------------------------------------------------------------
0x0700000000008B210B
(1 row(s) affected)
Converting float and real Data
Values of float are truncated when they are converted to any integer type.
When you want to convert from float or real to character data, using the STR string function is usually more useful than CAST( ). This is because STR enables more control over formatting. For more information, see STR (Transact-SQL) and Built-in Functions (Transact-SQL).
Converting money Data
When you convert to money from integer data types, units are assumed to be in monetary units. For example, the integer value of 4 is converted to the money equivalent of 4 monetary units.
The following example converts smallmoney and money values to varchar and decimal data types, respectively.
USE AdventureWorks2008R2;
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
Here is the result set.
SM_MONEY VARCHAR
------------------------------
3148.29
(1 row(s) affected)
MONEY DECIMAL
----------------------
3148
(1 row(s) affected)
Converting decimal and numeric Data
For the decimal and numeric data types, SQL Server considers each specific combination of precision and scale as a different data type. For example, decimal(5,5) and decimal(5,0) are considered different data types.
In Transact-SQL statements, a constant with a decimal point is automatically converted into a numeric data value, using the minimum precision and scale necessary. For example, the constant 12.345 is converted into a numeric value with a precision of 5 and a scale of 3.
Converting from decimal or numeric to float or real can cause some loss of precision. Converting from int, smallint, tinyint, float, real, money, or smallmoney to either decimal or numeric can cause overflow.
By default, SQL Server uses rounding when converting a number to a decimal or numeric value with a lower precision and scale. However, if the SET ARITHABORT option is ON, SQL Server raises an error when overflow occurs. Loss of only precision and scale is not sufficient to raise an error.
Converting integer Data
When integers are implicitly converted to a character data type, if the integer is too large to fit into the character field, SQL Server enters ASCII character 42, the asterisk (*).
Integer constants greater than 2,147,483,647 are converted to the decimal data type, not the bigint data type. The following example shows that when the threshold value is exceeded, the data type of the result changes from an int to a decimal.
SELECT 2147483647 / 2 AS Result1, 2147483649 / 2 AS Result2 ;
Here is the result set.
Result1 Result2
1073741823 1073741824.500000
Converting Data Types by Using OLE Automation Stored Procedures
Because SQL Server uses Transact-SQL data types and OLE Automation uses Visual Basic data types, the OLE Automation stored procedures must convert the data that passes between them.
The following table describes SQL Server to Visual Basic data type conversions.
SQL Server data type |
Visual Basic data type |
|---|---|
char, varchar, text, nvarchar, ntext |
String |
decimal, numeric |
String |
bit |
Boolean |
binary, varbinary, image |
One-dimensional Byte() array |
int |
Long |
smallint |
Integer |
tinyint |
Byte |
float |
Double |
real |
Single |
money, smallmoney |
Currency |
datetime, smalldatetime |
Date |
Anything set to NULL |
Variant set to Null |
All single SQL Server values are converted to a single Visual Basic value with the exception of binary, varbinary, and image values. These values are converted to a one-dimensional Byte() array in Visual Basic. This array has a range of Byte(0 to length1) where length is the number of bytes in the SQL Server binary, varbinary, or image values.
These are the conversions from Visual Basic data types to SQL Server data types.
Visual Basic data type |
SQL Server data type |
|---|---|
Long, Integer, Byte, Boolean, Object |
int |
Double, Single |
float |
Currency |
money |
Date |
datetime |
String with 4000 characters or less |
varchar/nvarchar |
String with more than 4000 characters |
text/ntext |
One-dimensional Byte() array with 8000 bytes or less |
varbinary |
One-dimensional Byte() array with more than 8000 bytes |
image |