Example
Convert an expression to int:
sql
SELECT CONVERT(int, 25.65);
Definition and Usage
The CONVERT() function converts a value (of any type) into a specified datatype.
Tip: Also look at the CAST() function.
Syntax
text
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 0100mon dd yyyy hh:miAM/PMDefault 1101mm/dd/yyyy US 2102 yyyy.mm.ddANSI 3103 dd/mm/yyyyBritish/French 4104dd.mm.yyyyGerman 5105 dd-mm-yyyyItalian 6106dd mon yyyy- 7107Mon dd, yyyy- 8108hh:mm:ss- 9109mon dd yyyy hh:mi:ss:mmmAM (or PM)Default + millisec 10110mm-dd-yyyyUSA 11111 yyyy/mm/ddJapan 12112 yyyymmddISO 13113dd mon yyyy hh:mi:ss:mmm Europe (24 hour clock)> 14114hh:mi:ss:mmm24 hour clock 20120yyyy-mm-dd hh:mi:ssODBC canonical (24 hour clock) 21121yyyy-mm-dd hh:mi:ss.mmmODBC canonical (24 hour clock) 126yyyy-mm-ddThh:mi:ss.mmmISO8601 127yyyy-mm-ddThh:mi:ss.mmmZISO8601 (with time zone Z) 130dd mon yyyy hh:mi:ss:mmmAMHijiri 131dd/mm/yy hh:mi:ss:mmmAMHijiri Converting float to real: Value Explanation 0Maximum 6 digits (default) 18 digits 216 digits Converting money to character: Value Explanation 0No comma delimiters, 2 digits to the right of decimal 1Comma delimiters, 2 digits to the right of decimal 2 No comma delimiters, 4 digits to the right of decimal | ||
| 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 | |
| Value | Explanation | ||
| 0 | Maximum 6 digits (default) | ||
| 1 | 8 digits | ||
| 2 | 16 digits | ||
| 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 |
Technical Details
| Works in: | SQL Server (starting with 2008), Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse |
|---|
More Examples
Example
Convert an expression from one data type to another (varchar):
sql
SELECT CONVERT(varchar, 25.65);
Example
Convert an expression from one data type to another (datetime):
sql
SELECT CONVERT(datetime, '2017-08-25');
Example
Convert an expression from one data type to another (varchar):
sql
SELECT CONVERT(varchar,
'2017-08-25', 101);