SQL CONVERT() Vs TRY_CONVERT() function

The SQL Convert() and TRY_Convert() both are SQL Conversions Functions and similar to CAST() or TRY_CAST() functions with an additional optional parameter ‘Style‘.

SQL CONVERT() function:

The SQL CONVERT() is a SQL Conversions Function, using this function you can convert an expression from one data type to another.

If conversion succeeds, function returns the expression in the desired data type, otherwise it returns the Error.

Syntax:

CONVERT(data_type(length), expression, style)

Description:

• Datatype: Specify the Datatype to which you want to convert the expression
• Length: It is an optional parameter that specifies the length of expression data type
• Expression: Specify any valid expression that you want to convert
• Style: It is an optional parameter of integer type. You can use this parameter to define the style.

Example:

Conversion success:

---With Datatype & Expression 
SELECT CONVERT(VARCHAR, GETDATE()) AS 'Output_Success_1';

----With Datatype, Length & Expression
SELECT CONVERT(VARCHAR(50), GETDATE()) AS 'Output_Success_2';

--With Datatype, Length, Expression & Style
SELECT CONVERT(VARCHAR(50), GETDATE(), 101) AS 'Output_Success_3';

---- With Datatype, Length, Expression & Style
SELECT CONVERT(VARCHAR(50), GETDATE(), 108) AS 'Output_Success_4';

Output:

Conversion fail:

---If Conversion Fails, returns error
SELECT CONVERT(Int, '12/26/2019', 101) AS 'Output_Fail';

Output:

List of formatting style:

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

SQL TRY_CONVERT():

It is a SQL Conversions Function, using this function you can convert an expression from one data type to another.

If conversion succeeds, function returns the expression in the desired data type, otherwise it returns the NULL.

Syntax:

CONVERT(data_type(length), expression, style)

Description:

• Datatype: Specify the Datatype to which you want to convert the expression
• Length:  It is an optional parameter that specifies the length of expression data type.
• Expression: Specify any valid expression that you want to convert.
• Style: It is an optional parameter of integer type. You can use this parameter to define the style.

Example:

Conversion success:

---With Datatype & Expression 
SELECT TRY_CONVERT(VARCHAR, GETDATE()) AS 'Output_Success_1';

----With Datatype, Length & Expression
SELECT TRY_CONVERT(VARCHAR(50), GETDATE()) AS 'Output_Success_2';

--With Datatype, Length, Expression & Style
SELECT TRY_CONVERT(VARCHAR(50), GETDATE(), 101) AS 'Output_Success_3';

---- With Datatype, Length, Expression & Style
SELECT TRY_CONVERT(VARCHAR(50), GETDATE(), 108) AS 'Output_Success_4';

Conversion fail:

---If Conversion Fails, returns error
SELECT TRY_CONVERT(Int, '12/26/2019', 101) AS 'Output_Fail';

Output:

Hope you enjoyed the post, Your valuable feedback, question, or comments about this post are always welcome or you can leave us message on our contact form , we will revert to you asap.

Recommended SQL Server Post:

CAST() Vs TRY_CAST() function

SQL REPLICATE() function

Configuration Functions in SQL Server

Query Optimization Technique in SQL Server

SQL Commands

SQL Identity functions

Alter table statement

SQL Keys