totn SQL Server Functions

SQL Server: CONVERT Function

This SQL Server tutorial 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.

TIP: Use the TRY_CONVERT function to return a NULL (instead of an error) if the conversion fails.

Syntax

The syntax for the CONVERT function in SQL Server (Transact-SQL) is:

CONVERT( type [ (length) ], expression [ , style ] )

Parameters or Arguments

type
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.
length
Optional. The length of the resulting data type for char, varchar, nchar, nvarchar, binary and varbinary.
expression
The value to convert to another datatype.
style
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);
Result: 14          (result is truncated)

SELECT CONVERT(float, 14.85);
Result: 14.85       (result is not truncated)

SELECT CONVERT(varchar, 15.6);
Result: '15.6'

SELECT CONVERT(varchar(4), 15.6);
Result: '15.6'

SELECT CONVERT(float, '15.6');
Result: 15.6

SELECT CONVERT(datetime, '2014-05-02');
Result: '2014-05-02 00:00:00.000'

SELECT CONVERT(varchar, '05/02/2014', 101);
Result: '05/02/2014'