SQL Server CONVERT() Function

The SQL Server (Transact-SQL) 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.

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

Syntax

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

Parameters

data_type Required. Specify the datatype to convert to. See the data types of SQL Server. Note that the Alias data types cannot be used.

length Optional. Specify the length of the resulting data type, for data types that allow a user specified length. Default is 30.

expression Required. Specify the value to convert to another datatype.

style

Optional. An integer expression to specify the format used to convert between datatypes, such as a date format or string format. For a style value of NULL, NULL is returned.

Date and Time styles

For a date or time data type expression, style can can be one of the following values:

Value (without century)	Value (with century)	Description
	0 or 100	mon dd yyyy hh:miAM/PM (Default for datetime and smalldatetime)
1	101	1 = mm/dd/yy, 101 = mm/dd/yyyy (US standard)
2	102	2 = yy.mm.dd, 102 = yyyy.mm.dd (ANSI standard)
3	103	3 = dd/mm/yy, 103 = dd/mm/yyyy (British/French standard)
4	104	4 = dd.mm.yy, 104 = dd.mm.yyyy (German standard)
5	105	5 = dd-mm-yy, 105 = dd-mm-yyyy (Italian standard)
6	106	6 = dd mon yy, 106 = dd mon yyyy
7	107	7 = Mon dd, yy, 107 = Mon dd, yyyy
8 or 24	108	hh:mi:ss
	9 or 109	mon dd yyyy hh:mi:ss:mmmAM/PM (Default + milliseconds)
10	110	10 = mm-dd-yy, 110 = mm-dd-yyyy (USA standard)
11	111	11 = yy/mm/dd, 111 = yyyy/mm/dd (Japan standard)
12	112	12 = yymmdd, 112 = yyyymmdd (ISO standard)
	13 or 113	dd mon yyyy hh:mi:ss:mmm (Europe standard - 24 hour clock, Default + milliseconds)
14	114	hh:mi:ss:mmm (24 hour clock)
	20 or 120	yyyy-mm-dd hh:mi:ss (ODBC canonical - 24 hour clock)
	21 or 25 or 121	yyyy-mm-dd hh:mi:ss.mmm (ODBC canonical - 24 hour clock with milliseconds, Default for time, date, datetime2, and datetimeoffset)
22		mm/dd/yy hh:mi:ss AM/PM (US standard)
	23	yyyy-mm-dd (ISO8601 standard)
	126	yyyy-mm-ddThh:mi:ss.mmm (ISO8601 standard)
	127	yyyy-MM-ddThh:mm:ss.fffZ (ISO8601 standard with time zone Z)
	130	dd mon yyyy hh:mi:ss:mmmAM (Hijri standard)
	131	dd/mm/yyyy hh:mi:ss:mmmAM (Hijri standard)

float and real styles

For a float or real data type expression, style can can be one of the following values:

Value	Description
0	(Default) A maximum of 6 digits. Use in scientific notation, when appropriate.
1	Always 8 digits. Always use in scientific notation.
2	Always 16 digits. Always use in scientific notation.
3	Always 17 digits. Use for lossless conversion. With this style, every distinct float or real value is guaranteed to convert to a distinct character string. Note: Applies to: SQL Server (Starting in SQL Server 2016 (13.x)) and Azure SQL Database.

money and smallmoney styles

For a money and smallmoney data type expression, style can can be one of the following values:

Value	Description
0	(Default) No comma delimiters, 2 digits to the right of decimal, Example: 1234.56
1	Comma delimiters, 2 digits to the right of decimal, Example: 1,234.56
2	No comma delimiters, 4 digits to the right of decimal, Example: 1234.5678

Return Value

Returns the expression converted to another data_type.

Convert to INT

The CONVERT() function can be used to convert a value to a INT type. For example - in the example below, 123.456 is converted to INT datatype.

SELECT CONVERT(INT, 123.456);
Result: 123

Convert to DECIMAL

The CONVERT() function can be used to convert a value to a DECIMAL type. For example - in the example below, '123.456' is converted to DECIMAL datatype.

SELECT CONVERT(DECIMAL(5, 2), '123.456');
Result: 123.46

Convert to CHAR

The CONVERT() function can be used to convert a value to a CHAR type. For example - in the example below, 123 is converted to CHAR datatype.

SELECT CONVERT(CHAR(4), 123);
Result: '123'

Convert to NCHAR

The CONVERT() function can be used to convert a value to a NCHAR type. For example - in the example below, 123 is converted to NCHAR datatype.

SELECT CONVERT(NCHAR(10), 123);
Result: '123'

Convert to VARCHAR

The CONVERT() function can be used to convert a value to a VARCHAR type. For example - in the example below, 123 is converted to VARCHAR datatype.

SELECT CONVERT(VARCHAR, 123);
Result: '123'

Convert to DATE

The CONVERT() function can be used to convert a value to a DATE type. For example - in the example below, '2018-08-18' is converted to DATE datatype.

SELECT CONVERT(DATE, '2018-08-18');
Result: '2018-08-31'

Convert to TIME

The CONVERT() function can be used to convert a value to a TIME type. For example - in the example below, '10:38:42' is converted to TIME datatype.

SELECT CONVERT(TIME, '10:38:42');
Result: '10:38:42'

Convert to DATETIME

The CONVERT() function can be used to convert a value to a DATETIME type. For example - in the example below, '2018-08-18 10:38:42' is converted to DATETIME datatype.

SELECT CONVERT(DATETIME, '2018-08-18 10:38:42', 101);
Result: '2018-08-18 10:38:42'

❮ SQL Server Functions