CONVERT Function in SQL Server

by

In this SQL Server tutorial, I will show how to use the CONVERT function in SQL Server.

Where you will see how to use the CONVERT() function to convert the data type of the value to another data type in an explicit way. Also, you will learn how to apply the CONVERT() function on the column of the table.

CONVERT Function in SQL Server

The CONVERT() function changes the datatype of the given value or expression to a different datatype. For example, this function can convert the datatype of value from integer to decimal, string to integer, integer to string, string to date and time, etc.

The syntax is given below.

CONVERT(datatype, value_or_exp, style)

Where,

  • datatype: Name of the data type to which the given value or expression will be converted.
  • value_or_exp: It is the value or expression that needs to be converted into another data type.
  • style: It defines the format of a date or string based on the different standards.

CONVERT Function in SQL Server: Convert Integer to String

Let’s use the CONVERT() function and change the data type of the integer value to a string using the below query.

SELECT CONVERT(VARCHAR,9832) AS IntToString;
Convert Function in SQL Server Integer to String

As you can see in the above picture, the integer 9832 is converted to a string using the CONVERT() function.

Here VARCHAR is the target datatype which means changing the datatype of integer 9832 to string ‘9832’.

CONVERT Function in SQL Server: Convert String to Date

You can also convert the given string into a date using the CONVERT() function. For that use the below query.

SELECT CONVERT(DATE,'2023/02/25') AS StringToDate;
Convert Function in SQL Server String to Date

In the above query, the target datatype is DATE and the string value is ‘2023/02/25’. After the execution of the query, the CONVERT() function converts the string ‘2023/02/25’ to the date datatype 2023-02-25 as you can see in the above picture.

CONVERT Function in SQL Server: Convert Float to Varchar

If you have a float value and want to change its datatype to varchar (string), you can do that too using the CONVERT() function.

For example, use the below query to convert the float value 993.45 to varchar.

SELECT CONVERT(VARCHAR,993.45) AS FloatToString;
Convert Function in SQL Server Float to String

Using the above query the float value 993.45 is converted to string ‘993.45’ using the CONVERT() function as shown in the above picture. Here VARCHAR is the target datatype to which the given float value 993.45 has converted.

CONVERT Function in SQL Server: Convert Float to Int

The CONVERT() function also converts the datatype from float to integer. For that use the below query.

SELECT CONVERT(INT,1024.56) AS FloatToInt;
Convert Function in SQL Server Convert Float to Int

Look how the CONVERT() function changes the datatype of the float value 1024.56 to integer value 1024. Here the INT is the target datatype and the float value is 1024.56.

This is how you can use the CONVERT() function to convert the datatype of the provided value to another datatype.

CONVERT Function in SQL Server: Convert Table Column Value

Until now, you have learned how to convert a single value from one datatype to another using only the SELECT statement. Let’s see how to use the CONVERT() function with the table.

Suppose you have access to the FinancialAccounts table with columns AccountID, AccountName, and Balance shown below.

Convert Function in SQL Server Convert FinancialAccounts Table Column Value

The Balance column of the table contains the float value, and you need to convert these float values into integers. So for that use the below query.

SELECT AccountID, AccountName, 
CONVERT(Int,Balance) as FloatToInt 
FROM FinancialAccounts;
Convert Function in SQL Server Convert Table Column Value

After the execution of the above query, each float value of the Balance column is converted into an integer value, look at the above picture in the FloatToInt column.

For example, before conversion Checking Account balance was 2500.75 and after conversion to an integer, it became 2500.

This is how you can use the CONVERT() function table column to convert from one datatype to another datatype.

Conclusion

In this SQL Server tutorial, you covered how to convert the data type of the value to different data types such as you have converted the value from integer to string, float to integer, string to date, etc. Also converted the data type of the column value from float to integer.

You may like to read: