CONVERT function [Data type conversion]

Returns an expression converted to a supplied data type.

The CAST, CONVERT, HEXTOINT, and INTTOHEX functions can be used to convert to and from hexadecimal values. For more information on using these functions, see Converting to and from hexadecimal values.

Syntax
CONVERT( datatype, expression [ , format-style ] )
Parameters
  • datatype   The data type to which the expression is converted.

  • expression   The expression to be converted.

  • format-style   The style code to apply to the outputted value. Use this parameter when converting strings to date or time data types, and vice versa. The table below shows the supported style codes, followed by a representation of the output format produced by that style code. The style codes are separated into two columns, depending on whether the century is included in the output format (for example, 06 versus 2006).

Without century (yy) style codes With century (yyyy) style codes Output format
- 0 or 100 Mmm dd yyyy hh:nnAA
1 101 mm/dd/yy[yy]
2 102 [yy]yy.mm.dd
3 103 dd/mm/yy[yy]
4 104 dd.mm.yy[yy]
5 105 dd-mm-yy[yy]
6 106 dd Mmm yy[yy]
7 107 Mmm dd, yy[yy]
8 108 hh:nn:ss
- 9 or 109 Mmm dd yyyy hh:nn:ss:sssAA
10 110 mm-dd-yy[yy]
11 111 [yy]yy/mm/dd
12 112 [yy]yymmdd
- 13 or 113 dd Mmm yyyy hh:nn:ss:sss (24 hour clock, Europe default + milliseconds, 4-digit year )
- 14 or 114 hh:nn:ss:sss (24 hour clock)
- 20 or 120 yyyy-mm-dd hh:nn:ss (24-hour clock, ODBC canonical, 4-digit year)
- 21 or 121 yyyy-mm-dd hh:nn:ss.sss (24 hour clock, ODBC canonical with milliseconds, 4-digit year )
Remarks

If no format-style argument is provided, style code 0 is used.

For a description of the styles produced by each output symbol (such as Mmm), see date_format option [compatibility].

See also
Standards and compatibility
  • SQL/2003   Vendor extension.

Example

The following statements illustrate the use of format style.

SELECT CONVERT( CHAR( 20 ), OrderDate, 104 ) FROM SalesOrders;
OrderDate
16.03.2000
20.03.2000
23.03.2000
25.03.2000
...
SELECT CONVERT( CHAR( 20 ), OrderDate, 7 ) FROM SalesOrders;
OrderDate
Mar 16, 00
Mar 20, 00
Mar 23, 00
Mar 25, 00
...

The following statement illustrates conversion to an integer, and returns the value 5.

SELECT CONVERT( integer, 5.2 );