How To Convert a Numeric Expression from One Data Type to Another in SQL Server Transact-SQL?

There are 4 ways to convert a numeric expression from one data type to another data type:

• Implicit conversion by arithmetic operations - When arithmetic operations are performed on expressions of different data types, implicit data type conversion will be performed before the arithmetic operation on the expression with a lower data type rank.
• Implicit conversion by assignment operations - When a numeric expression is assigned to variable, column, or parameter of different data type, the expression will be converted to match the data type of the variable, column, or parameter.
• Explicit conversion using the CAST() function - A numeric expression can be explicitly converted to different data type using the CAST(expression AS data_type) function.
• Explicit conversion using the CONVERT() function - A numeric expression can be explicitly converted to different data type using the CONVERT(data_type, expression) function.

Some numeric data type conversion examples are provided in the tutorial exercise below:

-- Implicit conversion by an arithmetic operation
-- INT converted to NUMERIC
DECLARE @d NUMERIC(9,6);
SET @d = 1.0;
SELECT @d/3;
GO
0.33333333

-- Implicit conversion by an assignment operation
-- NUMERIC converted to INT
DECLARE @i INT;
DECLARE @d NUMERIC(9,3);
SET @d = 123.456;
SET @i = @d;
SELECT @i;
GO
123

-- Explicit conversion by CAST()
-- FLOAT converted to NUMERIC
DECLARE @pi FLOAT(24);
SET @pi = 3.1415927E+00;
SELECT CAST(@pi AS NUMERIC(5,2));
GO
3.14

-- Explicit conversion by CONVERT()
-- FLOAT converted to NUMERIC
DECLARE @pi FLOAT(24);
SET @pi = 3.1415927E+00;
SELECT CONVERT(NUMERIC(5,2), @pi);
GO
3.14

⇒ Converting Numeric Data Types by Assignment Operations in SQL Server

⇐ Arithmetic Operations with Different Data Types in SQL Server

⇑ Numeric Expressions and Functions in SQL Server Transact-SQL

⇑⇑ SQL Server Transact-SQL Tutorials