SQL Server FAQ - Converting Numeric Expressions from One Data Type to Another

Home >> FAQs/Tutorials >> SQL Server FAQ

By: FYIcenter.com

How To Convert a Numeric Expression from One Data Type to Another?

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

(Continued on next topic...)