SQL Server CONVERT() Function
THE WORLD'S LARGEST WEB DEVELOPER SITE
HTML CSS JAVASCRIPT SQL PHP BOOTSTRAP HOW TO JQUERY W3.CSS ANGULAR XML MORE FORUM EXAMPLES REFERENCES

SQL Server CONVERT() Function

❮ SQL Server Functions

Example

Convert an expression from one data type to another (integer):

SELECT CONVERT(int, 25.65);
Try it Yourself »

Definition and Usage

The CONVERT() function converts an expression from one data type to another data type.

Note: When converting 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.

Tip: See also the CAST() function.

Syntax

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

Technical Details

Works in: SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005

More Examples

Example

Convert an expression from one data type to another (varchar):

SELECT CONVERT(varchar, 25.65);
Try it Yourself »

Example

Convert an expression from one data type to another (datetime):

SELECT CONVERT(datetime, '2017-08-25');
Try it Yourself »

Example

Convert an expression from one data type to another (varchar):

SELECT CONVERT(varchar, '2017-08-25', 101);
Try it Yourself »

❮ SQL Server Functions