TRY_CONVERT Function in SQL Server

by

In this SQL Server tutorial, you will learn about the TRY_CONVERT function in SQL Server that allows you to change the data type of the given value.

You will understand the syntax of the TRY_CONVERT() function and use the function to convert the string to integer, integer to string, etc.

Also, you will change the format of the output string of the datetime value using the style code.

TRY_CONVERT Function in SQL Server

TRY_CONVERT works as the CONVERT() function but can handle the error if conversion fails. So, this function can convert the data type of one value to another. If the conversion is not possible, it returns the NULL values.

This function can be helpful when conversion might lead to errors, so instead of showing an error, it shows the NULL values. Thus maintaining the database’s integrity and stability.

The syntax is given below.

TRY_CONVERT ( data_type( length ), expression, style )
  • TRY_CONVERT(): It is the function that performs the conversion.
  • data_type(length): Name of the data type to which the given value or expression will be converted. Also, specify the size for the datatype, such as VARCHAR or NVARCHAR.
  • expression: It is the value that you want to convert.
  • style: The option and the integer value determine the output format.

Let’s take an example and understand how the TRY_CONVERT() function differs from the CONVERT() function.

SELECT TRY_CONVERT(VARCHAR, 20) AS IntToVarchar;
TRY_CONVERT Function in SQL Server

In the above query, the TRY_CONVERT() function takes the integer value as 20 and converts that value into another data type, VARCHAR.

Next, let’s convert the string value to a float value using the below query.

SELECT TRY_CONVERT(numeric(10,2), '934.33') AS StringToFloat;
TRY_CONVERT Function in SQL Server Converting String to Numeric

As you can see in the above output, the string value ‘934.33’ is converted to the numeric or float value 934.33 using the TRY_CONVERT() function. Here, the numeric(10,2) means to convert the value to 10 digits with two decimal places.

Again, let’s take one more example and convert the float value to int using the query below.

SELECT TRY_CONVERT(int, 9394.56) AS FloatToInt;
TRY_CONVERT Function in SQL Server Converting Float to Int

The above query converted the given decimal value 9394.56 to integer value 9394 using the TRY_CONVERT() function.

Let’s see what happens if conversion fails; use the query below.

SELECT TRY_CONVERT(int, 'usa') AS StringToFloat;
TRY_CONVERT Function in SQL Server Returns Null

As you can see in the output, the TRY_CONVERT() function returns due to an error because converting the string ‘usa’ into the integer is impossible here. Here, you need to understand that you can convert the string containing the numeric value into an integer or float.

But you can’t convert the string containing the name, characters or symbol to an integer. Thus, if the TRY_CONVERT() function cannot convert the given value, it returns the NULL values.

This is how to use the TRY_CONVERT function in SQL Server.

TRY_CONVERT Function in SQL Server Datetime to Date

If you have a datetime value that contains the date and time and want to convert that value into a date value, or you want to only the date value of the datetime value. Then, use the TRY_CONVERT() function in that case.

For example, you have a table GlobalSalesData with columns SaledID, ProductID, and SaleTime shown below.

TRY_CONVERT Function in SQL Server Datetime to Date GlobalSalesData Table

As you can see, the SaleTime column contains the datetime value, and you want only to use the date, so use the query below.

SELECT SaleID, ProductID, TRY_CONVERT(date, SaleTime) AS SaleTime
FROM GlobalSalesData;
TRY_CONVERT Function in SQL Server Datetime to Date

After converting the SaleTime column value to the date datatype, the result contains only the date for each product ID. For example, the sale time of the product with an ID equal to 10 is 2023-11-25.

This is how to convert the datetime value date using the TRY_CONVERT() function in SQL Server.

TRY_CONVERT Function in SQL Server with Different Style

You can also output the value into a different format using the style parameter of the TRY_CONVERT() function. This is the optional parameter and takes the integer value, and based on that integer value, it interprets the output format.

This parameter is helpful when applying the TRY_CONVERT() function on the datetime and date values because, using this, you can specify the format of the output datetime string.

It can also help when converting decimal and numeric data types, such as including decimal places in the integer value.

Below is the given different style code for the datetime conversion.

Style CodeDate Format
0 or 100Default mon dd yyy hh:miAM (or PM)
101U.S. mm/dd/yyyy
102ANSI yyyy.mm.dd
103British/French dd/mm/yyyy
104German dd.mm.yyyy
105Italian dd-mm-yyyy
106-dd mon yyyy
107-mon dd, yyyy
108hh:mm:ss
109mon dd yyyy hh:mi:ss:mmmAM (or PM)
110USA mm-dd-yyyy
111Japan yyyy/mm/dd
112ISO yyyymmdd
113Europe default + milliseconds dd mon yyyy hh:mi:ss:mmm(24h)
120 or 20ODBC canonical yyyy-mm-dd hh:mi:ss(24h)
121ODBC canonical (with milliseconds) default for time, date, datetime2, and datetimeoffset
126ISO8601 yyyy-mm-ddThh:mi:ss.mmm (no spaces)
127ISO8601 with time zone Z.
130Hijri dd mon yyyy hh:mi:ss:mmmAM
131Hijri dd/mm/yyyy hh:mi:ss:mmmAM

as style, you can use any of the codes according to your requirements. For example, you want to format the SaleTime datetime value to style code 103 (U.S. dd/mm/yyyy). So, for that, use the below query.

For example, if you have a date in format 2023-02-11, then to convert to U.S. format, use the below query.

SELECT TRY_CONVERT(date, '2023-02-11', 103);

The above query converts the given date format to 02/11/2023. This is how to change the format of the given date to a different format using the style parameter of the TRY_CONVERT() function.

Conclusion

In this SQL Server tutorial, you converted the data of given values from integer to string, string to integer, and float to integer. Also, convert the datetime value to the date value using the TRY_CONVERT() function. Finally, the style code in the TRY_CONVERT() function changed the output format.

You may like to read: