| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | helpviewer_keywords | dev_langs | monikerRange | ||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Data type conversion (Database Engine) |
Data type conversion (Database Engine) |
MikeRayMSFT |
mikeray |
07/23/2017 |
sql |
t-sql |
reference |
|
|
>=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current||=fabric |
[!INCLUDE sql-asdb-asdbmi-asa-pdw-fabricse-fabricdw]
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 [!INCLUDEtsql] result column, return code, or output parameter is moved into a program variable, the data must be converted from the [!INCLUDEssNoVersion] system data type to the data type of the variable.
When you convert between an application variable and a [!INCLUDEssNoVersion] result set column, return code, parameter, or parameter marker, the supported data type conversions are defined by the database API.
Data types can be converted either implicitly or explicitly.
Implicit conversions are not visible to the user. [!INCLUDEssNoVersion] 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.
GETDATE() implicitly converts to date style 0. SYSDATETIME() implicitly converts to date style 21.
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 [!INCLUDEtsql] program code to comply with ISO. Use CONVERT instead of CAST to take advantage of the style functionality in CONVERT.
The following illustration shows all explicit and implicit data type conversions that are allowed for [!INCLUDEssNoVersion] 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.
While the above chart illustrates all the explicit and implicit conversions that are allowed in SQL Server, it does not indicate the resulting data type of the conversion. When SQL Server performs an explicit conversion, the statement itself determines the resulting data type. For implicit conversions, assignment statements such as setting the value of a variable or inserting a value into a column result in the data type that was defined by the variable declaration or column definition. For comparison operators or other expressions, the resulting data type depends on the rules of data type precedence.
As an example, the following script defines a variable of type varchar, assigns an int type value to the variable, then selects a concatenation of the variable with a string.
DECLARE @string VARCHAR(10);
SET @string = 1;
SELECT @string + ' is a string.'The int value of 1 is converted to a varchar, so the SELECT statement returns the value 1 is a string..
The following example, shows a similar script with an int variable instead:
DECLARE @notastring INT;
SET @notastring = '1';
SELECT @notastring + ' is not a string.'In this case, the SELECT statement throws the following error:
Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value ' is not a string.' to data type int.
In order to evaluate the expression @notastring + ' is not a string.', SQL Server follows the rules of data type precedence to complete the implicit conversion before the result of the expression can be calculated. Because int has a higher precedence than varchar, SQL Server attempts to convert the string to an integer and fails because this string cannot be converted to an integer. If the expression provides a string that can be converted, the statement succeeds, as in the following example:
DECLARE @notastring INT;
SET @notastring = '1';
SELECT @notastring + '1'In this case, the string 1 can be converted to the integer value 1, so this SELECT statement returns the value 2. Note that the + operator becomes addition rather than concatenation when the data types provided are integers.
Some implicit and explicit data type conversions are not supported when you are converting the data type of one [!INCLUDEssNoVersion] 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.
The following topics describe the conversion behaviors exhibited by their corresponding data types:
- binary and varbinary (Transact-SQL)
- datetime2 (Transact-SQL)
- money and smallmoney (Transact-SQL)
- bit (Transact-SQL)
- datetimeoffset (Transact-SQL)
- smalldatetime (Transact-SQL)
- char and varchar (Transact-SQL)
- decimal and numeric (Transact-SQL)
- sql_variant (Transact-SQL)
- date (Transact-SQL)
- float and real (Transact-SQL)
- time (Transact-SQL)
- datetime (Transact-SQL)
- int, bigint, smallint, and tinyint (Transact-SQL)
- uniqueidentifier (Transact-SQL)
Because [!INCLUDEssNoVersion] uses [!INCLUDEtsql] data types and OLE Automation uses [!INCLUDEvbprvb] data types, the OLE Automation stored procedures must convert the data that passes between them.
The following table describes [!INCLUDEssNoVersion] to [!INCLUDEvbprvb] 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 [!INCLUDEssNoVersion] values are converted to a single [!INCLUDEvbprvb] value with the exception of binary, varbinary, and image values. These values are converted to a one-dimensional Byte() array in [!INCLUDEvbprvb]. This array has a range of Byte( 0 to length 1**)** where length is the number of bytes in the [!INCLUDEssNoVersion] binary, varbinary, or image values.
These are the conversions from [!INCLUDEvbprvb] data types to [!INCLUDEssNoVersion] 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 |
OLE Automation Stored Procedures (Transact-SQL)
CAST and CONVERT (Transact-SQL)
Data Types (Transact-SQL)
COLLATE (Transact-SQL)