SQL Server CONVERT

Data type conversion is a method used to convert the data type of a value into the other type. All programming languages must have some data type converting functions. Similarly, SQL Server allows the user to change the data type of a value either implicitly or explicitly. SQL Server performs implicit conversion for their internal needs. In contrast, database administrators or programmers perform the explicit conversion with the help of data conversion functions. This article will mainly focus on the use of a CONVERT function in Microsoft SQL Server.

The CONVERT function enables the user to convert the data type of a value into another type specified in the expression. The syntax of a CONVERT function in SQL Server is given below:

CONVERT ( data_type (length) , expr, style)  

In the syntax, we have used the following parameters:

• data_type: It represents the target data type in which we want to convert the expression. It can include the following data types as an input: bigint, int, smallint, tinyint, bit, decimal, numeric, money, float, datetime, char, nchar, varchar, nvarchar, text, ntext, binary, image, etc. We make sure that the input data type should not be an alias type.
• length: It is an optional parameter for specifying the length of the target data type. By default, its value is 30.
• expr: It specifies a valid expression that we are going to convert into another type.
• style: It is an optional value that indicates the output style of the converted value. We will get the NULL value when it is NULL. It is useful for the DATE data type format.

Return Value

It will return a value of the expression in which data type we want to convert with a specified style.

CONVERT Function Example

Let us understand the CONVERT function with various practical examples.

Convert FLOAT to INT

This example converts the data type of float value to an integer. Here we have first declared a float variable and then assign them float value. Next, we will convert the float value to an integer with the help of the CONVERT function and print the result

--Declare float variable  

DECLARE @floatvalue AS Float  

--Assign value into a float variable  

SET @floatvalue = 345.1346   

--Use Convert function for conversion  

SELECT CONVERT(INT, @floatvalue) AS Result

After execution, we will get the below output:

Convert Float to Varchar

This example converts the data type of float value to varchar. It is similar to the above example, except here we will convert float value to varchar with the help of the CONVERT function.

DECLARE @fvalue AS Float  

SET @fvalue = 345.1346   

--Conversion of float into varchar  

SELECT CONVERT(varchar, @fvalue) AS Result

After execution, we will get the below output:

Convert decimal to another decimal with different length

This example will convert the decimal number into another decimal number with zero scales with the help of the CONVERT function. The rounding and truncation behavior of this function works similarly to the CAST function in SQL Server.

DECLARE @decimalVal AS DECIMAL  

SET @decimalVal = 9.956  

SELECT CONVERT(DECIMAL(2, 0), @decimalVal) AS Result

After execution, we will get the below output:

Convert String to DATETIME

This example will convert the string ‘2021-04-26’ to a DATETIME value with the help of the CONVERT function.

SELECT CONVERT(DATETIME, '2021-04-26') AS Result;  

After execution, we will get the below output:

Convert DATETIME to Varchar

This example will convert the current date and time to a string with a specific style using the CONVERT function.

SELECT CONVERT(VARCHAR, GETDATE(), 13) AS Result;  

After execution, we will get the below output:

Convert function with table

Here we are going to see how to use the convert function in tables to filter the records. Let us first create a table “orders” using the below statements:

CREATE TABLE orders (  

    order_id INTEGER PRIMARY KEY IDENTITY,  

    customer_id INTEGER,  

    item TEXT,  

    price FLOAT,  

    prchase_date DATE  

);  

INSERT INTO orders (customer_id, item, price, prchase_date)  

    VALUES (1345, 'Laptop', 65000.00, '2021-04-26'),  

    (2432, 'Mobile', 25000.00, '2021-04-06'),  

    (1213, 'Hard Disk', 3000.00, '2021-04-16'),  

    (3213, 'Mouse', 1250.00, '2021-04-16'),  

    (4313, 'Modem', 3000.00, '2021-04-26');

We can verify the table using the SELECT statement:

In the above table, the purchase_date is in DATE datatype. If we want to get the item list between selected ranges of date, we can use the below statement.

SELECT * FROM orders     

WHERE prchase_date     

BETWEEN CONVERT(DATETIME, '2021-04-10') AND CONVERT(DATETIME, '2021-04-20');

Here is the output:

TRY_CONVERT Function

It is the advanced form of the CONVERT function in SQL Server. It is useful in the data conversion process to protect from getting data converting errors during the query execution. With the CONVERT function, it’s possible that we can get errors while converting data type into another form due to non-suitable or dirty data. But, these errors can be avoided by using the TRY_CONVERT function. The syntax of the CONVERT and TRY_CONVERT function have no differences. We will get the NULL result if we use the TRY_CONVERT function when the data conversion generates an error.

Let us see the below example where we will try to convert a string value into an integer data type by using the CONVERT function:

SELECT CONVERT(INT, 'Javatpoint') AS Result;  

When we execute this query, SQL Server will raise the following error:

However, SQL Server has a TRY_CONVERT function that protects from getting this type of error and returns the NULL value. See the below query:

SELECT TRY_CONVERT(INT, 'Javatpoint') AS Result;  

When we execute this query, SQL Server returns the NULL value instead of raising an error:

Difference between CAST and CONVERT Function

SQL Server always uses the CAST and CONVERT functions for data type conversion. However, they have some differences that we will be discussing below:

• CONVERT is not a part of the ANSI-SQL specification. It is, in reality, SQL implementation-specific. On the other hand, CAST is purely part of ANSI-SQL specification.
• Another difference between them is that the CONVERT function can accept an optional style parameter that specifies the format of the data type after conversion, whereas CAST cannot.
• CAST uses the ‘AS’ clause to separate the parameter, whereas CONVERT uses the comma(,) operator for separating the parameter.
• The CAST function can preserve decimal values during the conversion of decimal values into integers. If necessary, it can also truncate the decimal value. On the other hand, this functionality cannot achieve with the CONVERT function.