Convert data types in SQL

From: http://blog.163.com/liufupanhao@126/blog/static/128465170201111210446407/

Thank you for sharing your ideas!

 

1. Data type conversion: (1) Two conversion scenarios:
A: When the data between two result sets is compared or joined together, if the data types of the two result sets are different, they must be converted. B: When you return a result set from a Transact-SQL statement to a variable in a program, you must convert the type of the result set from the SQL server data type to the Data Type of the variable. (2) Two conversion methods:
A: Automatic implicit conversion (invisible to users ). For example, when a smallint variable is compared with an int variable, the smallint variable is implicitly converted to an int variable before the comparison. B: User-provided Explicit conversions (using cast or convert functions for Explicit conversions ). 2. Function syntax

Cast (expression as data_type [(length)])

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

Parameters

Expression any valid expression.

Data_type is the data type provided by the target system.

Length the length of the target data type (valid when the target data type is nchar, nvarchar, Char, varchar, binary, or varbinary ). Convert is 30 characters by default.

The style of the target data format. If the style is null, the returned result is also null.

Note:

A: The target data type cannot use the alias data type. Nchar, nvarchar, Char, varchar, binary, or varbinary

B: The style of the target data format is valid only when the following types are converted.

(1) convert datetime or smalldatetime data to character data (nchar, nvarchar, Char, varchar, nchar, or nvarchar data type)

(2) convert character data of known date or time format to datetime, smalldatetime, or string format

(3) converts float, real, money, or smallmoney data into character data (nchar, nvarchar, Char, varchar, nchar, or nvarchar data type ).

3. Remarks (by msdn) (1) implicit and explicit conversion behaviors of the dense data type, especially varchar, nvarchar, and varbinary data types, are the same as those of the small data type. However, the following principles should be taken into account:

The conversion from image to varbinary (max) is implicit conversion, while the conversion between text and varchar (max), ntext, and nvarchar (max) is also implicit conversion. The conversion from the sequence data type (such as varchar (max) to the small value data type (such as varchar) is implicit conversion, but if the sequence is too large to the small value of the specified length, A truncation is generated. Conversions from varchar, nvarchar, or varbinary to corresponding shard data types are implicitly executed. The conversion from SQL _variant data type to external data type is an explicit conversion. The sequence data type cannot be converted to the SQL _variant data type. (2) When you explicitly or implicitly convert an XML data type to a string or binary data type, the content of the XML data type will be serialized according to a set of rules.

(3) text and Image Data Types

Automatic conversion of text and image data types is not supported. You can explicitly convert text data to character data and convert image data to binary or varbinary, but the maximum length is 8000 bytes. If an incorrect conversion is attempted, for example, converting a character expression containing letters to an int, SQL Server Returns an error message.

(4) If the output of cast or convert is a string and the input is a string, the output will have the same sorting rule and sorting rule label as the input. If the input is not a string, the default database sorting rule and the default mandatory sorting rule label are used. See sorting rule priority (TRANSACT-SQL ).

To assign different sorting rules to the output, apply the Collate clause to the Result Expression of the cast or convert function. For example:

Select cast ('abc' as varchar (5) Collate french_cs_as

(5) When the truncation and rounding results convert character or binary expressions (char, nchar, nvarchar, varchar, binary, or varbinary) to other data types, data can be truncated, only some data is displayed, or an error is returned (the result is too short to be displayed ). Except for the conversions shown in the following table, conversions to Char, varchar, nchar, nvarchar, binary, and varbinary are truncated.