Convert data type of SQL Server variables in SQL Server

How to convert the data type of the SQL Server variables?

To convert the data types of a variable, we can use CAST function. CAST function is only used to change the data type of the value.

DECLARE @year int

SELECT @year = 1975

SELECT @year + ' year'

In the above code snippet, we are declaring @year variable of int type. If we want to append a string to the variable in the output, it throws error.

This error comes because @year is of integer type and we are trying add string into it. Remember that integer can be only added to ant numeric type not string. If we try to add two string, it gets concatenated.

Replacing the last line to below code will work

SELECT CAST(@year as varchar(5)) + ' year'

Here, we are converting the @year variable to varchar type and then we are trying to add ‘ year’ string that will work as both are of string type and it will easily get concatenated.

Instead of varchar, we can use other data types like decimal, float, single etc depending on whether it is convertible or not. The thumb rule is any numeric data types can be converted into string type. However a string may or may not get converted into numeric data types.

DECLARE @year varchar(20)

SELECT @year = '1975'

SELECT CAST(@year as int) + 20

In above case, we are converting string to integer and adding 20, so the result would be 1995. Here, the value of @year is ‘1975’ string that is a perfect valid string so it can be converted into integer.

However, if we change the value of @year to some other alphabets, it throws error.