CONVERT

Example

Transform an expression into an integer data type.

SELECT CONVERT(int, 25.65);

Definition and Usage

The CONVERT() function in SQL Server converts a value of any type into a specified data type, offering flexibility in data manipulation and formatting.

Tip: Consider using the CAST() function for similar data type conversions.

Syntax

CONVERT(data_type(length), expression, style)

Parameter Values

Parameter

Description

datatype

Mandatory. Specifies the target data type to which the expression will be converted. Options include: 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. Specifies the length or size of the resulting data type, applicable to conversions involving char, varchar, nchar, nvarchar, binary, and varbinary.

expression

Mandatory. The value that is to be converted to a different data type.

style

Optional. The format used for conversions between data types, such as when converting datetime values to character strings.

Without century	With century	Input/Output	Standard
0	100	mon dd yyy hh:miAM/PM	Default
1	101	mm/dd/yyyy	US
2	102	yyyy.mm.dd	ANSI
3	103	dd/mm/yyyy	British/French
4	104	dd.mm.yyyy	German
5	105	dd-mm-yyyy	Italian
6	106	dd mon yyyy	–
7	107	Mon dd, yyyy	–
8	108	hh:mm:ss	–
9	109	mon dd yyyy hh:mi:ss:mmmAM (or PM)	Default + millisec
10	110	mm-dd-yyyy	USA
11	111	yyyy/mm/dd	Japan
12	112	Yyyymmdd	ISO
13	113	dd mon yyyy hh:mi:ss:mmm	Europe (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
	127	yyyy-mm-ddThh:mi:ss.mmmZ	ISO8601 (with time zone Z)
	130	dd mon yyyy hh:mi:ss:mmmAM	Hijri
	131	dd/mm/yy hh:mi:ss:mmmAM	Hijri

Transforming a floating-point value into a real number.

Value	Explanation
0	Maximum 6 digits (default)
1	8 digits
2	16 digits

Converting a monetary value to a character string.

Value	Explanation
0	Ensure there are no comma separators, and restrict the decimal precision to two digits.
1	Include comma separators and maintain two digits to the right of the decimal point.
2	Do not use comma separators and maintain four digits to the right of the decimal point.

Technical Details

Works in:

SQL Server (from 2008 onward), Azure SQL Database, Azure SQL Data Warehouse, and Parallel Data Warehouse

More Examples

Example

Change the data type of an expression to varchar.

SELECT CONVERT(varchar, 25.65);

Example

Transform an expression into a datetime data type.

SELECT CONVERT(datetime, ‘2017-08-25’);

Example

Change the data type of an expression to varchar.

SELECT CONVERT(varchar, ‘2017-08-25’, 101);