The CONVERT function converts an expression of one datatype to another datatype. Also obtains a variety of special date formats.
CONVERT (datatype[(length)], expression [, style])
where
Note For datatypes where specifying length (n) is optional ¾ (binary[(n)], char[(n)], varbinary[(n)], and varchar[(n)]) ¾ SQL Server uses 30 for the length.
The maximum allowable length is 255.
| Without century (yy) |
With century (yyyy) |
Standard |
Output |
|---|---|---|---|
| - | 0 or 100 (*) | Default | mon dd yyyy hh:miAM (or PM) |
| 1 | 101 | USA | mm/dd/yy |
| 2 | 102 | ANSI | yy.mm.dd |
| 3 | 103 | British/French | dd/mm/yy |
| 4 | 104 | German | dd.mm.yy |
| 5 | 105 | Italian | dd-mm-yy |
| 6 | 106 | - | dd mon yy |
| 7 | 107 | - | mon dd, yy |
| 8 | 108 | - | hh:mm:ss |
| - | 9 or 109 (*) | Default milliseconds | mon dd yyyy hh:mi:ss:mmmAM (or PM) |
| 10 | 110 | USA | mm-dd-yy |
| 11 | 111 | JAPAN | yy/mm/dd |
| 12 | 112 | ISO | yymmdd |
| - | 13 or 113 (*) | Europe default milliseconds | dd mon yyyy hh:mm:ss:mmm(24h) |
| 14 | 114 | - | hh:mi:ss:mmm(24h) |
| * The default values (style 0 or 100, 9 or 109, and 13 or 113) always return the century (yyyy). |
|||
When converting to character data from smalldatetime, the styles that include seconds or milliseconds show zeros in these positions. You can truncate unwanted date parts when converting from datetime or smalldatetime values by using an appropriate char or varchar datatype length.
SQL Server automatically handles certain datatype conversions. For example, if you compare a character expression and a datetime expression, or a smallint expression and an int expression, or character expressions of different lengths, SQL Server makes the conversion automatically for the comparison. You are not required to use CONVERT; however, it is never wrong to use the CONVERT function even when you are comparing two expressions of exactly the same datatype. CONVERT can be used in a select list or in a WHERE clause.
When concatenating noncharacter, nonbinary expressions, always use the CONVERT function. For example:
SELECT 'The date is ' + CONVERT(varchar(12), getdate())
Automatic conversion is not supported for the text and image datatypes. You can explicitly convert text data to character data, and image data to binary or varbinary, but the maximum length you can specify is 255. If you attempt a conversion that is not possible (for example, if you try to convert a character expression that includes letters to an int), SQL Server generates an error message.
When converting between types with a different number of decimal places, the value is rounded to the most precise digit. The result of the following is 10:
SELECT CONVERT(int, 10.6496)
This table shows the possible datatype conversions.
| Key: | I Implicit conversion | |
| E Explicit conversion, CONVERT function must be used | ||
| N Conversion not allowed | ||
| * Converting from decimal or numeric to decimal or numeric requires CONVERT when a loss of precision or scale will occur. |
||
| - Conversion of a datatype to itself; allowed but meaningless | ||
Convert to binary to display the internal representation of a value. If lengths differ, the binary value is padded with zeros on the left.
You can convert int, smallint, and tinyint to binary or varbinary, but if you convert the binary value back to an integer value, it may be different from the original integer value. In addition, if the integer is longer than the specified binary length, it is truncated. For example, a conversion from a long binary to an int value uses only the 4 rightmost bytes of the binary value. The following example shows why binary and varbinary datatypes should be used only for hexadecimal numbers that are interpreted as type streams, and should not be used in calculations.
SELECT CONVERT(int,0x0001000008ae36f0) SELECT CONVERT(int,0x00010000) ------ 145635056 (1 row affected) ------ 65536 (1 row affected)
Converting to bit promotes any nonzero value to 1.
When character expressions are converted to a character datatype of a different size, values too long for the new datatype are truncated.
Character expressions being converted to an approximate numeric datatype can include optional exponential notation (a lowercase e or uppercase E followed by an optional plus () or minus (-) sign and then a number).
Character expressions being converted to an exact numeric datatype must consist of digits, a decimal point, and an optional plus () or minus (-) sign. Leading blanks are ignored.
Character expressions being converted to money datatypes can also include an optional decimal point and dollar sign ($).
When converting to datetime, 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.
Values of float are truncated when converted to any integer type.
When you are converting from float or real to character data, the string function STR( ) is usually a better choice than CONVERT( ), because STR( ) gives you more control over formatting. For more information, see the String Functions topic.
When converting to money from integer datatypes, units are assumed to be dollars. For example, the integer value of 4 is converted to the money equivalent of 4 dollars.
For the decimal and numeric datatypes, SQL Server considers each specific combination of precision and scale as a different datatype. For example, decimal(5,5) and decimal(5,0) are considered different datatypes.
In Transact-SQL statements, a constant with a decimal point is automatically converted into a numeric data value, using the minimum precision and scale necessary. For example, the constant 12.345 is converted into a numeric value with a precision of 5 and a scale of 3. This behavior is different from earlier versions of SQL Server, which converted constants with a decimal point to float. Therefore, calculations can return slightly different (but more precise) results.
Converting from decimal or numeric to float or real can result in some loss of precision. Converting from int, smallint, tinyint, float, real, money, or smallmoney to either decimal or numeric can result in overflow.
If you perform arithmetic on two exact numeric values, v1 and v2, where v1 has a precision of p1 and a scale of s1, and where v2 has a precision of p2 and s2, the precision and scale of the result depends on the operator (with an absolute maximum of @@MAX_PRECISION) and is determined as follows:
| Operation | Precision | Scale |
|---|---|---|
| v1 v2 | max(s1,s2) max(p1-s1,p2-s2) | max(s1,s2) |
| v1 - v2 | max(s1,s2) max(p1-s1,p2-s2) | max(s2,s2) |
| v1 * v2 | s1 s2 (p1-s1) (p2-s2) 1 | s1 s2 |
| v1 / v2 | max(s1+p2-s2+1,6) p1 p2 1 | max(s1+p2-s2+1,6) |
By default, SQL Server uses rounding when converting a number to a decimal or numeric value with a lower precision and scale. However, if the ARITHABORT option is enabled (using the SET statement), SQL Server cancels a query and raises a precision error when an exact numeric operation would result in a loss of precision and scale.
This example shows how to convert data for display.
SELECT title, CONVERT(char(12), ytd_sales)
FROM titles
This example converts sales data to character data prior to performing a string comparison.
SELECT title, ytd_sales
FROM titles
WHERE CONVERT(char(20), ytd_sales) LIKE '1%'
This example converts the current date to style 3, dd/mm/yy.
SELECT CONVERT(char(12), GETDATE(), 3)