SQL Server CAST and CONVERT functions

1.sqlserver CAST and CONVERT functions

Explicitly converts an expression of a data type to another data type. CAST and CONVERT provide similar functionality.

Grammar

Using CAST:

CAST (expression as data_type)

Using CONVERT:

CONVERT (data_type[(length)], expression [, style])

Parameters

Expression

Is any valid Microsoft SQL Server expression. For more information, see expressions.

Data_type

The data types provided by the target system, including bigint and sql_variant. You cannot use a user-defined data type. For more information about the available data types, see Data types.

Length

Optional parameters for nchar, nvarchar, char, varchar, binary, or varbinary data types.

Style

A date format style whereby DateTime or smalldatetime data is converted to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data type) or a string format style that will float, Real, money, or smallmoney data is converted to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types).

Explicitly converts an expression of a data type to another data type. For more information about the available data types, see Data types. A date format style whereby DateTime or smalldatetime data is converted to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data type) or a string format style that will float, Real, money, or smallmoney data is converted to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types). Output when converted to character data.

Implicit conversions are not visible to the user.

SQL Server automatically converts data from one data type to another data type. For example, if a smallint variable is compared to an int variable, the smallint variable is implicitly converted to an int variable before it is compared.

An explicit conversion uses the cast or CONVERT function.

The cast and CONVERT functions convert a numeric value from one data type (local variable, column, or other expression) to another data type. For example, the following cast function converts the numeric $157.27 to the string ' $157.27 ': The cast ($157.27 as VARCHAR) cast function is based on the SQL-92 standard and takes precedence over convert.

When converting from one SQL Server object's data type to another, some implicit and explicit data type conversions are not supported. For example, the nchar value cannot be converted to an image value at all. NChar can only be explicitly converted to binary, and the implicit conversion to binary is not supported. NChar can be explicitly or implicitly converted to nvarchar.

When working with the sql_variant data type, SQL Server supports implicitly converting objects with other data types to sql_variant types. However, SQL Server does not support objects that are implicitly converted from sql_variant data to other data types.

SQL Server CAST and CONVERT functions