SQL Server Convert Function

In this tutorial, I will explain how to use the SQL Server convert function.

Data conversion is an important aspect of databases. It is an effective way to manage and organize data into different data types, allowing the user to access, view, or modify data when required.

Let’s get started to get more about this.

What is SQL Server Convert Function

It is important to know what the SQL Server convert function is before using it.

As the name suggests, it is to convert one data type to another data type with formatting. If the conversion succeeds, the function returns the converted value else it will produce if it fails.

Depending upon the SQL Version we are using, the convert function works differently. In the earlier versions of SQL, namely 2005, 2008, and 2012, the convert function will return a datatype in an expression.

It will return a Null value when the datatype argument is Null. But in the latest version, the function returns Null if the third parameter is Null.

Data Conversion Part

First, we can separate the data conversion process into two parts: implicit and Explicit conversions.

SQL Server performs implicit conversion for internal needs, while the database programmer or admin performs explicit conversion simultaneously.

Syntax

CONVERT(data_type(length), expression, style)

• data_type — This is used to define the resulting data type the user needs in the end. It will support multiple data types, such as int, bigint, smallint, tinyint, bit, decimal, numeric, money, small money, float, real, DateTime, smalldatetime, char, varchar, text, char, varchar, text, etc.
• Length – This is an optional parameter that accepts integer values. It defines the length of the resulting data type. By default, the value is 30.
• expression – It is used to define the original expression a user actually wants to convert.
• Style – It is used to convert between data types, such as a date or string format.

Example for SQL Convert Function

SELECT CONVERT (int, 69.65) as Result;	

So here we are, trying to convert the float value into an integer value. Here, the input expression is 69.65, which is the float data type. So, by using the convert function query, we are changing the data type of 69.65 from float to integer.

After successful execution, it will return an integer value of 69, as shown in the result below.

For example, consider the table. Here, I have an HRInfo table where I want to convert salary into an integer. Use the syntax below. If you look at the below screenshot, the salary value is converted into a whole integer value.

select *,convert (Int,salary) as 'Final salary' from HRInfo

Convert Function in SQL Server

We will see how to convert the function in the table. For example, here, I have a PatientInfo table with columns for PatientID, PatientName, and PatientBills.

Let’s see how to convert the function in SQL Server. The patient bills contain float values, which we have to convert into integer values.

Use the below query to change the value from float to integer.

SELECT PatientID, PatientName, 
CONVERT(Int,PatientBills) as FloatToInt 
FROM PatientInfo;

From this, the float data type is converted into an Integer data type.

This is how we have to convert the table float value into an integer value. Look at the column FloatToInt, which contains all the values in integer form.

Convert Float to Integer

The convert() function will convert the float datatype to an integer data type. Use the below query.

SELECT CONVERT(INT,1509.56) AS FloatToInt;

Here, the target data type is integer. It will convert and execute as below. The convert() function converts the float data type value 1509.56 into the integer data type 1509.

This is the simple method of converting float value into integer value.

Convert Integer into Decimal

Follow the below syntax to convert integers into decimal values in SQL Server Management Studio.

 SELECT CONVERT(DECIMAL(5, 2), 150);

Check the below values, which are changed into decimal.

Convert String to Numeric Value

Follow the below syntax to convert the function from string to Numeric. Here, I have taken the Employee table as a reference, where the age column is a numeric value.

SELECT CAST(Age AS INTEGER) FROM Employee;

Convert String to Date by Date Function

Below is the syntax to convert string to date in SQL Server Management Studio.

Here, we have to pass 103 as the style number (103 corresponds to dd/mm/yyyy date format); it will provide the output.

SELECT CONVERT(DATETIME,'12/05/2024',103)

Conclusion

From this, you have learned how to use the convert function in SQL Server Management Studio. You learned how to use the convert() function to convert a float value to an integer and also how to use the convert() function with table columns.

• MSSQL Create Database
• SQL Server 2019 Features
• How to Create a Table in SQL Server Management Studio