SQL Server convert integer to string + 12 Examples

In this SQL Server tutorial, we will learn details on sql server convert integer to string and various examples like below:

• SQL Server Convert int to string/varchar
• SQL Server Convert int to date yyyymm
• SQL Server Convert int to date yyyymmdd
• SQL Server Convert int column to date
• SQL Server Convert int to nvarchar
• SQL Server Convert int to decimal/float
• SQL Server Convert int to decimal 2 places
• SQL Server Convert int to time
• SQL Server Convert int to a bit
• SQL Server Convert excel int to date
• SQL Server Convert int time to DateTime
• SQL Server Convert Datetime / date to int

Here I have used sql server management studio and sql server 2019 express edition.

SQL Server Convert int to string/varchar

In SQL Server, there are 3 different ways through which we can convert an expression of integer data type to varchar. And in this section, we will discuss all the methods with the help of an example.

Converting int to string/varchar using Cast()

The Cast() in SQL Server, is a function that converts a value of one data type to another. It has the following syntax.

CAST(expression AS datatype(length))

The Cast() function accepts two values, the first is the expression whose data type a user wants to change, and the second is the resulting data type that a user wants.

Example

DECLARE @Number INT  
SET @Number=22062021  
SELECT CAST(@Number AS varchar(10)) AS Num1 

So, in the above example, we have declared a variable of integer data type and assigned a value to the variable. After this, we are using the Cast() function to convert the variable to the varchar data type of length 10. And the query will return the following result.

Converting int to string / varchar using Convert()

This Convert() in SQL Server is also a conversion function used to convert a value of one data type to another. But the Convert() function is more efficient than Cast() because it provides additional formatting by using styles parameter. The Convert() function has the following syntax.

CONVERT(datatype(length), expression, style)

The style parameter is used to define the formatting option on the resulting expressions. And it accepts integer values to define the formatting.

Example

DECLARE @Number INT  
SET @Number=22062021  
SELECT CONVERT(varchar(10),@Number) AS Num2 

In the above example, we have declared an integer variable and assigned a value to it. After this, we are using the Convert() function to convert the variable to the varchar data type of length 10. And the query will return the following result.

Converting to string/varchar using STR()

STR() is another function in SQL Server that accepts number values and returns them as a varchar expression. And the character value is right-justified, with a specified length and decimal precision. The STR() function has the following syntax.

STR(number, length, decimals)

The STR() function accepts three arguments, first is the number value that a user wants to convert. The second is the total length of the resulting expression, and the last is the number of decimal points to appear in the resulting expression.

As the result from STR() function is right-justified, so to get a correct string expression, we have to use LTRIM() function. The LTRIM() function removes the leading spaces from an input string expression.

Example

DECLARE @Number INT  
SET @Number=22062021  
SELECT LTRIM(STR(@Number,10)) AS Num3

• Now, in the above example, we have declared an integer variable and assigned a value to it.
• After this, we are using the STR() function within the SELECT statement to convert the variable to a varchar data type of length 10.
• And then, we are using the result from the STR() function in LTRIM() function to remove all the leading spaces.

In the last, the query will return the following result.

Read: SQL Server Convert Datetime to String

SQL Server Convert int to date yyyymm

In SQL Server, we cannot directly convert any integer value to yyyymm date format. For this implementation, the integer should follow a proper yyyymmdd format, and then we have to use Convert() and Left() function together to convert it to the yyyymm format.

DECLARE @Number INT  
SET @Number=20210622  
SELECT @Number AS 'Integer', 
       LEFT(CONVERT(varchar, @Number,112),6) AS 'YYYYMM'

In the above query, first, we have declared an integer variable with a value. After this, we are using the Convert() function to convert the number to yyyymmdd format. And then, we are using the result of the Convert() function in the LEFT() function to select 6 characters from the starting of the string.

After successfull query execution, we will get the following output.

Read: SQL Server DateTime vs Datetime2

SQL Server Convert int to date yyyymmdd

Now to convert an integer data type to yyyymmdd date format in SQL Server, we can use the Convert() function. In Convert() function, we can specify style code to format the result in yyyymmdd format. For this, consider the following example.

DECLARE @Number INT  
SET @Number=20210622  
SELECT @Number AS 'Integer', 
       CONVERT(varchar, @Number,112) AS 'YYYYMMDD'

In the above example, we are using the Convert() function to change an integer variable to a varchar expression. And we have also defined the style code as 112 in the Convert() function. After execution, it will return the following result.

SQL Server Convert int column to date

In SQL Server, we cannot directly convert a column of integer data type to date. For this, we have to use the combination of Cast() and Convert() functions. And we can follow the following syntax.

CONVERT(Date, CAST(columne_name AS CHAR(8)))

In the above syntax, first, we are using the Cast() function to convert the integer column to a character data type. And then, we are using the character expression in Convert() function to be converted into a date value.

Let’s understand the implementation with the help of an example and consider the following sample table.

The sample table contains an integer column named “Date_in“, which carries date representation in integer values (yyyymmdd). And we will convert this integer column to a date value by using the following query.

SELECT [Date_In], 
       CONVERT(Date, CAST([Date_In] AS CHAR(8)))
       AS 'New_date'
FROM Sample

So in the above query, we are converting the Date_In column to character value using the Cast() function. And then, we are using the Convert() function to change the character expression to date.

After successful query execution, the query will return the following result.

SQL Server Convert int to nvarchar

There are mainly 2 functions available in SQL Server, through which we can convert an integer data type to a nvarchar expression. And in this section, we are going to discuss both with the help of an example.

Using Cast() function

Cast() is one of the simplest conversion functions available in SQL Server that we can use to convert an integer to nvarchar. And for this, we can follow the following syntax.

CAST(number_expression as nvarchar)

Now to demonstration consider the following example.

DECLARE @Number int
SET @Number = 43210
SELECT @Number AS 'int_value',
       CAST(@Number as nvarchar) AS 'nvarchar_value'

In the example, first, we have declared an integer variable with some value. After this, we are using the Cast() function within the SELECT statement to convert the variable to a nvarchar expression. After execution, the query will return the following result.

Using Convert() function

To convert an integer to a nvarchar expression using the Convert() function, we can follow the following syntax.

CONVERT(nvarchar, number_expression)

Now to demonstration consider the following example.

DECLARE @Number int
SET @Number = 43210
SELECT @Number AS 'int_value',
       CONVERT(nvarchar, @Number) AS 'nvarchar_value'

In the example, first, we have declared an integer variable with some value. After this, we are using the Convert() function within the SELECT statement to convert the variable to a nvarchar expression. After execution, the query will return the same value, the only difference will be in the value data type.

Read: SQL Server Replace Function

SQL Server Convert int to decimal/float

There are mainly 2 methods through which we can convert an integer data type to either a decimal or float expression. The first is by using the Cast() function and the second is by using the Convert() function. We will discuss both methods with the help of an example.

Using Cast() function

DECLARE @Number int
SET @Number = 43
SELECT @Number AS 'int_value',
       CAST(@Number AS decimal(7,3)) AS 'decimal_value'

• Now in the above example, first, we have declared a variable with some integer value.
• After this, we are using the Cast() function within the SELECT statement to convert the integer variable to a decimal expression.
• The decimal() accepts 2 arguments, the first is the total number of digits in the value, and the second is the number of digits after the decimal point. So the converted value will have 3 decimal points.

After successful execution, we will get the following result.

Using Convert() function

DECLARE @Number int
SET @Number = 99
SELECT @Number AS 'int_value',
       CONVERT(decimal(7,4),@Number) AS 'decimal_value'

• Now in the above example, first, we have declared a variable with some integer value.
• After this, we are using the Convert() function within the SELECT statement to convert the integer variable to a decimal expression.
• And we have also defined 2 values in decimal(), the first is the total number of digits in the value, and the second is the number of digits after the decimal point. So the converted value will have 4 decimal points.

After successful execution, we will get the following result.

SQL Server Convert int to decimal 2 places

We can easily convert any integer value to decimal data type either by using Cast() or by using the Convert() function.

The decimal data type accepts 2 parameters, first is the precision, which is used to define the total number of digits in the expression including both sides of the decimal point. And the second parameter is for scale, which defines the number of digits after the decimal point in the expression.

decimal (precision [,scale])

Now let’s understand this implementation with help of an example and consider the following sample employee table.

The above table contains a salary column that carries integer values representing the salary of each employee. And we will use the Convert() function to change this integer column to a decimal expression having decimal up to 2 places.

SELECT [Salary], 
       CONVERT(decimal(8,2),[Salary]) AS 'decimal_value'
FROM Employee

In the above query, we are using the Convert() function within the SELECT statement to change the Salary column to decimal. And in decimal(), we have defined the precision as 8 and scale as 2, which means that the resulting value can have a maximum length of 8 and will have 2 decimal places.

And the query will return the following output.

Read: SQL Server Convert String to Date

SQL Server Convert int to time

In SQL Server, we cannot directly convert an integer value to a time expression. But it is possible by using a combination of multiple functions available in SQL Server. So there can multiple approaches through which we can convert int to time.

In this section, we will discuss one of the possible solutions to achieve the result with the help of an example given below.

DECLARE @var int
SET @var = 17465958

SELECT DATEADD(hour, (@var / 1000000) % 100,
       DATEADD(minute, (@var / 10000) % 100,
       DATEADD(second, (@var / 100) % 100,
       DATEADD(millisecond, (@var % 100) * 10, CAST('00:00:00' as time(2)))))) 

• In the above example, first, we have declared an integer variable with some formatted value (hhmmssnn).
• After this, we are using the CAST() function to get the basic time format (00:00:00.00), and then we are extracting the hours, minutes, seconds, and milliseconds value.
• In the end, we are using the DATEADD() function to add the extracted values to the basic time format (00:00:00.00).

After execution, the query will return the followin result.

SQL Server Convert int to a bit

In SQL Server, converting any non-zero value to a bit will return 1, and there are mainly two methods through which we can convert an integer value to either a 0 or 1. And we will discuss both with the help of an example.

Using Cast()

DECLARE @val INT = 37;
SELECT @val AS 'int_value',
       CAST(@val AS BIT) AS 'bit_value'

In the above example, first, we have declared an integer variable with some value. After that we are using the Cast() function to convert the variable to a bit value. And as we are converting 37 to a bit value, the Cast() function will return 1 as an output.

Using Convert()

DECLARE @val INT = 77;
SELECT @val AS 'int_value',
       CONVERT(BIT, @val) AS 'bit_value'

In the above example, first, we have declared an integer variable with some value. After that we are using the Convert() function to convert the variable to a bit value. And as we are converting 77 to a bit value, the Convert() function will return 1 as an output.

Read: How to create functions in SQL Server Management Studio

SQL Server Convert int time to DateTime

In SQL Server, we cannot directly convert a column of integer data type to DateTime data type. For this, we have to use the combination of Cast() and Convert() functions. And we can follow the following syntax for conversion.

CONVERT(Datetime, CAST(columne_name AS CHAR(8)))

• Now let’s understand the implementation with the help of an example. And for example, we are taking the previous sample table having the “Date_In” integer column.
• And we will try to convert the integer column to DateTime by using the following query.

SELECT [Date_in], 
       CONVERT(datetime, CAST([Date_in] AS CHAR(8)), 101) 
       AS [Datetime_value]
FROM Sample

So in the above query, first, we are using the Cast() function to change the integer column to 8 character expression. And then, we are using the Convert() function to convert the character expression to a DateTime expression.

After successful query execution, we will get the following result.

SQL Server Convert excel int to date

Excel keeps track of dates as serial numbers that can be utilized in calculations. This serial number starts by assigning 1st January 1900 as 1. And if we are exporting our data from excel to SQL Server, we need to convert the excel date serial number to date format.

Now there are many tricks through which we can convert any excel serial number to a date format. In this section, we are going to understand one of the ways through which we can easily make the conversion. And for the implementation, we can follow the following syntax

CAST(serial_number - 2 as DateTime)

• The SQL Server also recognizes integer values as date expressions. The only difference between excel and SQL Server is that in excel 1 is recognized as 1900-1-1, whereas in SQL Server, 0 is recognized as 1900-1-1.
• So for correct conversion, we are using the Cast() function, and we are subtracting 2 from the serial number to get the result.

Example

Now for demonstration, consider the following employee table, which contains the joining date column representing excel serial number as joining date.

Now we are going to use the following query to convert the excel serial number column to date.

SELECT [Joining_date], 
       CAST([Joining_date] - 2 AS datetime) 
	   AS 'New_Date' 
FROM Employee

In the above query, we are using the Cast() function to convert the Joining_date column to a DateTime column and after successful execution, we will get the following result.

Read: SQL Server Convert Datetime to date + Examples

SQL Server Convert Datetime/date to int

In Convert() function, we generally use 112 as style code for the ‘YYYYMMDD‘ date format, but the resulting expression is of string data type. Now to convert the date or a DateTime value to an integer data type, we can follow the following syntax.

CAST(CONVERT(char(8), date_expression, 112) as int)

• For the conversion, first, we are using the Convert() function to change the date to the ‘YYYYMMDD’ character expression.
• After this, we are using the Cast() function to convert the character expression to an integer value.
• We can also use Convert() function instead of Cast() to change the character expression to date.

Example

Now for demonstration, we are going to convert the joining date of an employee to an integer value by using the following query.

SELECT [Joining_date],
       CAST(CONVERT(char(8), [Joining_date], 112) as int)
	   AS 'int_value'
FROM Employee

And after successful, execution we will get the following result.

You may like the following sql server tutorials:

• SQL Server Convert Function
• SQL Server Substring Function

In this SQL Server tutorial, we have learned How to convert an integer to a string, convert an integer to a date field, and cover the following topics.

• SQL Server Convert int to string / varchar
• SQL Server Convert int to date yyyymm
• SQL Server Convert int to date yyyymmdd
• SQL Server Convert int column to date
• SQL Server Convert int to nvarchar
• SQL Server Convert int to decimal/float
• SQL Server Convert int to decimal 2 places
• SQL Server Convert int to time
• SQL Server Convert int to a bit
• SQL Server Convert excel int to date
• SQL Server Convert int time to DateTime
• SQL Server Convert Datetime / date to int