Conversion Functions
Use the conversion functions, CAST and CONVERT, to convert expressions of one data type to another data type when these conversions are not performed automatically by Microsoft® SQL Server™ 2000. These conversion functions are also used to obtain a variety of special data formats. Either of the conversion functions can be used in the select list, in the WHERE clause, and anywhere an expression is allowed.
Use CAST rather than CONVERT if you want Transact-SQL program code to comply with SQL-92. Use CONVERT rather than CAST to take advantage of the style functionality in CONVERT.
When using either CAST or CONVERT, two pieces of information are required:
- The expression to convert (for example, the sales report requires the sales data to be converted from monetary data to character data).
- The data type to convert the given expression to, for example, varchar or any other SQL Server-supplied data type.
Unless you store the converted value, a conversion is valid only for the length of the CAST or CONVERT function.
This example uses CAST in the first SELECT statement and CONVERT in the second SELECT statement to convert the title column to a char(50) column, to make the results more readable:
USE pubs
SELECT CAST(title AS char(50)), ytd_sales
FROM titles
WHERE type = 'trad_cook'
Or
USE pubs
SELECT CONVERT(char(50), title), ytd_sales
FROM titles
WHERE type = 'trad_cook'
Here is the result set: (for either query)
ytd_sales
----------------------------------------- -----------
Onions, Leeks, and Garlic: Cooking Secrets of the 375
Fifty Years in Buckingham Palace Kitchens 15096
Sushi, Anyone? 4095
(3 row(s) affected)
In this example, the ytd_sales column, an int column, is converted to a char(20) column so that it can be used with the LIKE predicate:
USE pubs
SELECT title, ytd_sales
FROM titles
WHERE CAST(ytd_sales AS char(20)) LIKE '15%'
AND type = 'trad_cook'
Here is the result set:
Title ytd_sales
----------------------------------------- ---------
Fifty Years in Buckingham Palace Kitchens 15096
(1 row(s) affected)
SQL Server automatically handles certain data type conversions. For example, if you compare a char and a datetime expression, or a smallint and an int expression, or char expressions of different lengths, SQL Server converts them automatically. This is called an implicit conversion. You do not have to use the CAST function for these conversions. However, it is acceptable to use the CAST when:
- Two expressions are exactly the same data type.
- Two expressions are implicitly convertible.
- It is necessary to explicitly convert the data types.
If you attempt a conversion that is not possible (for example, converting a char expression that includes letters to int), SQL Server displays an error message.
If you do not specify a length when converting for the data type, SQL Server automatically supplies a length of 30.
When converting to datetime or smalldatetime, SQL Server rejects all values it cannot recognize as dates (including dates earlier than January 1, 1753). You can convert datetime values to smalldatetime when the date is in the proper range (from January 1, 1900 through June 6, 2079). The time value is rounded to the nearest minute.
Converting to bit changes any nonzero value to 1.
When converting to money or smallmoney, integers are assumed to be monetary units. For example, the integer value of 4 is converted to the money equivalent of 4 dollars (for us_english, the default language). Numbers to the right of the decimal in floating-point values are rounded to four decimal places for money values. Expressions of data types char or varchar that are being converted to an integer data type must consist only of digits and an optional plus or minus sign (+ or -). Leading blanks are ignored. Expressions of data types char or varchar converted to money can also include an optional decimal point and leading dollar sign ($).
Expressions of data types char or varchar that are being converted to float or real can also include optional exponential notation (e or E, followed by an optional + or - sign, and then a number).
When character expressions are converted to a data type of a different size, values too long for the new data type are truncated, and SQL Server displays an asterisk (*) in both the osql utility and SQL Query Analyzer. When numeric expressions are too long for the new data type to display, values are truncated. This is an example of character truncation:
USE pubs
SELECT SUBSTRING(title, 1, 25) AS Title, CONVERT(char(2), ytd_sales)
FROM titles
WHERE type = 'trad_cook'
Here is the result set:
Title
------------------------- --
Onions, Leeks, and Garlic *
Fifty Years in Buckingham *
Sushi, Anyone? *
(3 row(s) affected)
When converting between data types in which the target data type has fewer decimal places than the source data type, the value is truncated. For example, the result of CAST(10.3496 AS money) is $10.35.
You can explicitly convert text data to char or varchar, and image data to binary or varbinary. Because these data types are limited to 8,000 characters, you are limited to the maximum length of the character and binary data types, 8,000 characters. You can explicitly convert ntext data to nchar or nvarchar, but the maximum length is 4,000 characters. If you do not specify the length, the converted value has a default length of 30 characters. Implicit conversion is not supported.
The style Parameter
The style parameter of CONVERT provides a variety of date display formats when converting datetime data to char or varchar. The number you supply as the style parameter determines how the datetime data is displayed. The year can be displayed in either two or four digits. By default, SQL Server supplies a two-digit year. To display a four-digit year including the century (yyyy), even if the year data was stored by using a two-digit year format, add 100 to a style value to get a four-place year.
This example shows CONVERT with the style parameter:
SELECT CONVERT(char(12), GETDATE(), 3)
This statement converts the current date to style 3, dd/mm/yy.