The conversion function, CONVERT, is used to convert expressions of one datatype to another datatype whenever these conversions are not performed automatically by SQL Server. It is also used to obtain a variety of special date formats. The conversion function can be used in the select list, in the WHERE clause, and anywhere an expression is allowed.
The CONVERT function has the following syntax:
CONVERT(datatype [(length)], expression [, style])
Here's an example that uses CONVERT in the select list to convert the title column to a char(50) column so the results are more readable:
SELECT CONVERT(char(50), title), ytd_sales FROM titles WHERE type = 'trad_cook'
title |
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 clause:
SELECT title, ytd_sales
FROM titles
WHERE convert(char(20), ytd_sales) LIKE '15%'
AND type = 'trad_cook'
title |
ytd_sales |
----------------------------------- |
--------- |
Fifty Years in Buckingham Palace Kitchens |
15096 |
(1 row(s) affected)
SQL Server automatically handles certain datatype conversions. For example, if you compare a char expression and a datetime expression, or a smallint expression and an int expression, or char expressions of different lengths, SQL Server converts them automatically. You need not use the CONVERT function for these conversions. However, it is never wrong to use the CONVERT function, even when you are comparing two expressions of exactly the same datatype.
If you attempt a conversion that is not possible (for example, if you try to convert a char expression that includes letters to int), SQL Server displays an error message.
For a complete list of possibilities for converting datatypes, see Functions in the Microsoft SQL Server Transact-SQL Reference.
If you do not specify a length when converting for the datatype to which the expression is to be converted, 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 (January 1, 1900, to June 6, 2079). The time value is rounded up to the nearest minute.
Converting to bit changes any non-zero 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 datatypes char or varchar that are being converted to an integer datatype must consist only of digits and an optional plus or minus sign ( or -). Leading blanks are ignored. Expressions of datatypes char or varchar that are being converted to money can also include an optional decimal point and dollar sign ($).
Expressions of datatypes 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 expressions are converted to a datatype of a different size, values too long for the new datatype are truncated, and SQL Server displays an asterisk (*). Here's an example:
SELECT title, CONVERT(char(2), ytd_sales) |
FROM titles |
WHERE type = 'trad_cook' |
title |
--------------------------------------------------------------- |
Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean |
Fifty Years in Buckingham Palace Kitchens |
Sushi, Anyone? |
(3 row(s) affected) |
When converting between datatypes with a different number of decimal points, the value is truncated. For example, the result of CONVERT(money, 10.3496) is $10.35.
You can explicitly convert text columns to char or varchar columns, and image columns to binary or varbinary columns. Because these datatypes are limited to 255 characters, you are limited to the maximum length of the character and binary datatypes, 255 characters. If you do not specify the length, the converted value has a default length of 30 characters. Implicit conversion is not supported.