SQL Server Tutorial SQL Server Advanced SQL Server Database SQL Server References

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 100mon dd yyyy hh:miAM/PM (Default for datetime and smalldatetime)
11011 = mm/dd/yy, 101 = mm/dd/yyyy (US standard)
21022 = yy.mm.dd, 102 = yyyy.mm.dd (ANSI standard)
31033 = dd/mm/yy, 103 = dd/mm/yyyy (British/French standard)
41044 = dd.mm.yy, 104 = dd.mm.yyyy (German standard)
51055 = dd-mm-yy, 105 = dd-mm-yyyy (Italian standard)
61066 = dd mon yy, 106 = dd mon yyyy
71077 = Mon dd, yy, 107 = Mon dd, yyyy
8 or 24108hh:mi:ss
9 or 109mon dd yyyy hh:mi:ss:mmmAM/PM (Default + milliseconds)
1011010 = mm-dd-yy, 110 = mm-dd-yyyy (USA standard)
1111111 = yy/mm/dd, 111 = yyyy/mm/dd (Japan standard)
1211212 = yymmdd, 112 = yyyymmdd (ISO standard)
13 or 113dd mon yyyy hh:mi:ss:mmm (Europe standard - 24 hour clock, Default + milliseconds)
14114hh:mi:ss:mmm (24 hour clock)
20 or 120yyyy-mm-dd hh:mi:ss (ODBC canonical - 24 hour clock)
21 or 25 or 121yyyy-mm-dd hh:mi:ss.mmm (ODBC canonical - 24 hour clock with milliseconds, Default for time, date, datetime2, and datetimeoffset)
22mm/dd/yy hh:mi:ss AM/PM (US standard)
23yyyy-mm-dd (ISO8601 standard)
126yyyy-mm-ddThh:mi:ss.mmm (ISO8601 standard)
127yyyy-MM-ddThh:mm:ss.fffZ (ISO8601 standard with time zone Z)
130dd mon yyyy hh:mi:ss:mmmAM (Hijri standard)
131dd/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:

ValueDescription
0(Default) A maximum of 6 digits. Use in scientific notation, when appropriate.
1Always 8 digits. Always use in scientific notation.
2Always 16 digits. Always use in scientific notation.
3Always 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:

ValueDescription
0(Default) No comma delimiters, 2 digits to the right of decimal, Example: 1234.56
1Comma delimiters, 2 digits to the right of decimal, Example: 1,234.56
2No 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