Loading lesson path

SQL•SQL References

SQL Server CONVERT() Function

Flash cards

Review the key moves

1/4

Core idea

What is the main idea behind SQL Server CONVERT() Function?

Lesson checks

Practice each idea before moving on

Short Mimo-style checks built from this lesson's code, terms, and sequence.

1Quick choice

Which statement best captures the main point of this lesson?

2Fill blank

Complete the missing token from the example code.

___ CONVERT(int, 25.65);

3Order

Put the learning moves in the order that makes the concept easiest to apply.

The CONVERT() function converts a value (of any type) into a specified datatype.

Definition and Usage

SQL Server CONVERT() Function

Example

SELECT CONVERT(int, 25.65);

Definition and Usage

The CONVERT() function converts a value (of any type) into a specified datatype.

Tip

Also look at 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 0 100 mon dd yyyy 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 Hijiri 131 dd/mm/yy hh:mi:ss:mmmAM Hijiri 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 1 Comma delimiters, 2 digits to the right of decimal 2 No comma delimiters, 4 digits to the right of decimal
Without century	With century	Input/Output	Standard
0	100	mon dd yyyy 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	Hijiri
131	dd/mm/yy hh:mi:ss:mmmAM	Hijiri
Value	Explanation
0	Maximum 6 digits (default)
1	8 digits
2	16 digits
Value	Explanation
0	No comma delimiters, 2 digits to the right of decimal
1	Comma delimiters, 2 digits to the right of decimal
2	No comma delimiters, 4 digits to the right of decimal

Without century	With century	Input/Output	Standard
0	100	mon dd yyyy 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	Hijiri
131	dd/mm/yy hh:mi:ss:mmmAM	Hijiri

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

Value	Explanation
0	No comma delimiters, 2 digits to the right of decimal
1	Comma 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 (starting with 2008), Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse

SQL Server COALESCE() Function

SQL Server CURRENT_USER Function

Loading lesson path

SQL•SQL References

SQL Server CONVERT() Function

Flash cards

Review the key moves

1/4

Core idea

What is the main idea behind SQL Server CONVERT() Function?

Lesson checks

Practice each idea before moving on

Short Mimo-style checks built from this lesson's code, terms, and sequence.

1Quick choice

Which statement best captures the main point of this lesson?

2Fill blank

Complete the missing token from the example code.

___ CONVERT(int, 25.65);

3Order

Put the learning moves in the order that makes the concept easiest to apply.

The CONVERT() function converts a value (of any type) into a specified datatype.

Definition and Usage

SQL Server CONVERT() Function

Example

SELECT CONVERT(int, 25.65);

Definition and Usage

The CONVERT() function converts a value (of any type) into a specified datatype.

Tip

Also look at 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 0 100 mon dd yyyy 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 Hijiri 131 dd/mm/yy hh:mi:ss:mmmAM Hijiri 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 1 Comma delimiters, 2 digits to the right of decimal 2 No comma delimiters, 4 digits to the right of decimal
Without century	With century	Input/Output	Standard
0	100	mon dd yyyy 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	Hijiri
131	dd/mm/yy hh:mi:ss:mmmAM	Hijiri
Value	Explanation
0	Maximum 6 digits (default)
1	8 digits
2	16 digits
Value	Explanation
0	No comma delimiters, 2 digits to the right of decimal
1	Comma delimiters, 2 digits to the right of decimal
2	No comma delimiters, 4 digits to the right of decimal

Without century	With century	Input/Output	Standard
0	100	mon dd yyyy 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	Hijiri
131	dd/mm/yy hh:mi:ss:mmmAM	Hijiri

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

Value	Explanation
0	No comma delimiters, 2 digits to the right of decimal
1	Comma 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 (starting with 2008), Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse

SQL Server COALESCE() Function

SQL Server CURRENT_USER Function