Document directory
Data Type Conversion
In Transact-SQL, there may be two levels of data type conversion:
- When the data of one object is moved to another object, or the data between two objects is compared or combined, the data may have to be converted from the Data Type of one object to the Data Type of another object.
- When you move data from a Transact-SQL result column, return code, or output parameter to a program variable, you must convert the data type from Microsoft SQL Server to that variable.
There are two types of data conversion:
- Implicit conversions are invisible to users. SQL Server automatically converts data from one data type to another. For example, if
SmallintVariable andIntVariable, thisSmallintVariables are implicitly converted
IntVariable.
- Explicit conversions use the CAST or CONVERT functions.
The CAST and CONVERT functions CONVERT numeric values from one data type (local variable, column, or other expression) to another data type. For example, the following CAST function converts the value $157.27 to the string '$157.27 ':
CAST ( $157.27 AS VARCHAR(10) )
CAST functions are based on SQL-92 standards and take precedence over CONVERT.
When the number of decimal places in a conversion is different from the data type, the result value is sometimes truncated and sometimes rounded.
Converted data type |
Converted to Data Type |
Action |
Numeric |
Numeric |
Round |
Numeric |
Int |
Truncation |
Numeric |
Money |
Round |
Money |
Int |
Round |
Money |
Numeric |
Round |
Float |
Int |
Truncation |
Float |
Numeric |
Round |
Float |
Datetime |
Round |
Datetime |
Int |
Rounding |
For example, the following conversion result is10
:
SELECT CAST(10.6496 AS int)
During data type conversion, if the decimal point of the target data type is smaller than the decimal point of the source data type, the value is truncated. For example, the following conversion result is$10.3497
:
SELECT CAST(10.3496847 AS money)
When non-numeric char, nchar, varchar, or nvarchar data is converted to int, float, numeric, or decimal data, SQL Server Returns an error message. When the empty string ("") is converted to numeric or decimal, SQL Server also returns an error.
Some implicit and explicit data types cannot be converted from the Data Type of one SQL Server object to another. For example,NcharThe value cannot be converted
ImageValue.NcharCan only be explicitly convertedBinary, Implicitly converted
BinaryIs not supported.NcharIt can be converted explicitly or implicitlyNvarchar.
When processingSQL _variantSQL Server supports implicit conversion of objects with other data types
SQL _variantType. However, SQL Server does not supportSQL _variantData is implicitly converted to objects of other data types.
During conversion between Application variables and SQL Server result set columns, return code, parameters, or parameter tags, the supported data type conversion is defined by the database application interface.