SQL Server Data Type Conversion Function

SQL string functions-data type conversion functions

In general, SQL Server automatically converts data types, for example, you can directly compare the character data type or expression with the datatime data type or expression. When integer, smallint, or tinyint is used in the expression, SQL Server can also convert integer data types or expressions to smallint data types or expressions, which are called implicit conversions. If you cannot determine whether SQL server can complete implicit conversion or use other data types that cannot be implicitly converted, you need to use the data type conversion function for explicit conversion. There are two such functions: ・ Cast () The syntax of the cast () function is as follows: Cast (<expression> as <data _ type> [length]) ・ Convert () The convert () function syntax is as follows: Convert (<data _ type> [length], <expression> [, style]) Reminder: 1. data_type is the data type defined by the SQL server system. The custom data type cannot be used here. 2. length is used to specify the data length. The default value is 30. 3. Convert the char or varchar type to an integer type such as Int or samllint. The result must be a value with a positive sign (+) or a negative sign. 4. The conversion from text to Char or varchar is a maximum of 8000 characters, that is, the char or varchar data type is of the maximum length. 5. Convert the data stored in the image type to the binary or varbinary type, which can contain a maximum of 8000 characters. 6. Convert the integer to the money or smallmoney type and process it according to the currency unit of the defined country, such as RMB, USD, and pound. 7. Bit type conversion converts a non-zero value to 1, and is still stored as bit type. 8. If you try to convert the data type to different lengths, the converted value will be truncated and "+" will be displayed after the converted value to identify the occurrence of this truncation. You can use the style option of the convert () function to display the date and time in different formats. Style is the conversion style number provided by the SQL server system when converting datatime and smalldatetime to a string. Different style numbers have different output formats. Example: Cast ([field name] As varchar (4000 )) Convert (varchar (4000), [field name])