SQL Server CAST and CONVERT functions

Source: Internet
Author: User
Tags numeric value string format

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.