Home » SQL Server » SQL CONVERT() Vs TRY_CONVERT() function

SQL CONVERT() Vs TRY_CONVERT() function

Convert VS Try

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:

SQL Convert() function output

SQL Convert() function output

Conversion fail:

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

Output:

SQL Output

SQL 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:

SQL TRY_CONVERT() function

SQL TRY_CONVERT() function

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

Leave a Reply