Sql Server Convert Function
SQL Server: CONVERT Function
In this post explains how to use the CONVERT function in SQL Server (Transact-SQL) with syntax and examples.
Description
In SQL Server (Transact-SQL), the CONVERT function converts an expression from one datatype to another datatype. If the conversion fails, the function will return an error. Otherwise, it will return the converted value.
Syntax
The syntax for the CONVERT function in SQL Server (Transact-SQL) is:
CONVERT( type [ (length) ], expression [ , style ] )Parameters or Arguments
- type
- length
- expression
- style
The datatype that you wish to convert expression to. It 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.
Optional. The length of the resulting data type for char, varchar, nchar, nvarchar, binary and varbinary.
The value to convert to another datatype.
Optional. The format used to convert between datatypes, such as a date format or string format. It can be one of the following values:
Converting datetime to character
| Value (without century) | Value (with century) | Explanation |
|---|---|---|
| 0 | 100 | mon dd yyyy hh:miAM/PM (Default) |
| 1 | 101 | mm/dd/yyyy (US standard) |
| 2 | 102 | yy.mm.dd (ANSI standard) |
| 3 | 103 | dd/mm/yy (British/French standard) |
| 4 | 104 | dd.mm.yy (German standard) |
| 5 | 105 | dd-mm-yy (Italian standard) |
| 6 | 106 | dd mon yy |
| 7 | 107 | Mon dd, yy |
| 8 | 108 | hh:mi:ss |
| 9 | 109 | mon dd yyyy hh:mi:ss:mmmAM/PM |
| 10 | 110 | mm-dd-yy (USA standard) |
| 11 | 111 | yy/mm/dd (Japan standard) |
| 12 | 112 | yymmdd (ISO standard) |
| 13 | 113 | dd mon yyyy hh:mi:ss:mmm (Europe standard - 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 standard) | |
| 127 | yyyy-mm-ddThh:mi:ss:mmmZ (ISO8601 standard) | |
| 130 | dd mon yyyy hh:mi:ss:mmmAM/PM (Hijri standard) | |
| 131 | dd/mm/yy hh:mi:ss:mmmAM/PM (Hijri standard) |
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 (ie: 1234.56) |
| 1 | Comma delimiters, 2 digits to the right of decimal (ie: 1,234.56) |
| 2 | No comma delimiters, 4 digits to the right of decimal (ie: 1234.5678) |
Note
- When casting from a float or numeric to an integer, the CONVERT function will truncate the result. For other conversions, the CONVERT function will round the result.
- See also the TRY_CONVERT, CAST, and TRY_CAST functions.
Applies To
The CONVERT function can be used in the following versions of SQL Server (Transact-SQL):
- SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005
Example
Let's look at some SQL Server CONVERT function examples and explore how to use the CONVERT function in SQL Server (Transact-SQL).
For example:
SELECT CONVERT(int, 14.85);
Output: 14 (result is truncated)
SELECT CONVERT(float, 14.85);
Output: 14.85 (result is not truncated)
SELECT CONVERT(varchar, 15.6);
Output: '15.6'
SELECT CONVERT(varchar(4), 15.6);
Output: '15.6'
SELECT CONVERT(float, '15.6');
Output: 15.6
SELECT CONVERT(datetime, '2014-05-02');
Output: '2014-05-02 00:00:00.000'
SELECT CONVERT(varchar, '05/02/2014', 101);
Output: '05/02/2014'