SQL data type conversion (cast () and Convent () function)

Document directory

• Data Type Conversion

 

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.