Definition and Usage
The CONVERT() function converts a value (of any type) into a specified datatype.
Tip: Also look at the CAST() function.
Syntax
CONVERT(data_type(length), expression, style)
Parameter Values
| Value |
Description |
| data_type |
Required. The datatype to convert expression to. Can be one of the following: bigint, int, smallint, tinyint, bit, decimal, numeric, money, smallmoney, float, real, datetime, smalldatetime, char, varchar, text, nchar, nvarchar, ntext, binary, varbinary, or image |
| (length) |
Optional. The length of the resulting data type (for char, varchar, nchar, nvarchar, binary and varbinary) |
| expression |
Required. The value to convert to another data type |
| style |
Optional. The format used to convert between data types, such as a date or string format. Can be one of the following values:
Converting datetime to character:
| Without century |
With century |
Input/Output |
Standard |
| 0 |
100 |
mon dd yyyy hh:miAM/PM |
Default |
| 1 |
101 |
mm/dd/yyyy |
US |
| 2 |
102 |
yyyy.mm.dd |
ANSI |
| 3 |
103 |
dd/mm/yyyy |
British/French |
| 4 |
104 |
dd.mm.yyyy |
German |
| 5 |
105 |
dd-mm-yyyy |
Italian |
| 6 |
106 |
dd mon yyyy |
- |
| 7 |
107 |
Mon dd, yyyy |
- |
| 8 |
108 |
hh:mm:ss |
- |
| 9 |
109 |
mon dd yyyy hh:mi:ss:mmmAM (or PM) |
Default + millisec |
| 10 |
110 |
mm-dd-yyyy |
USA |
| 11 |
111 |
yyyy/mm/dd |
Japan |
| 12 |
112 |
yyyymmdd |
ISO |
| 13 |
113 |
dd mon yyyy hh:mi:ss:mmm |
Europe (24 hour clock)> |
| 14 |
114 |
hh:mi:ss:mmm |
24 hour clock |
| 20 |
120 |
yyyy-mm-dd hh:mi:ss |
ODBC canonical (24 hour clock) |
| 21 |
121 |
yyyy-mm-dd hh:mi:ss.mmm |
ODBC canonical (24 hour clock) |
| |
126 |
yyyy-mm-ddThh:mi:ss.mmm |
ISO8601 |
| |
127 |
yyyy-mm-ddThh:mi:ss.mmmZ |
ISO8601 (with time zone Z) |
| |
130 |
dd mon yyyy hh:mi:ss:mmmAM |
Hijiri |
| |
131 |
dd/mm/yy hh:mi:ss:mmmAM |
Hijiri |
Converting float to real:
| Value |
Explanation |
| 0 |
Maximum 6 digits (default) |
| 1 |
8 digits |
| 2 |
16 digits |
Converting money to character:
| Value |
Explanation |
| 0 |
No comma delimiters, 2 digits to the right of decimal |
| 1 |
Comma delimiters, 2 digits to the right of decimal |
| 2 |
No comma delimiters, 4 digits to the right of decimal |
|
