Home >> FAQs/Tutorials >> SQL Server FAQ

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

By: FYIcenter.com

(Continued from previous topic...)

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...)

  1. What Is an Expression?
  2. What Are Arithmetic Operators?
  3. What Happens to an Arithmetic Operation with Two Different Data Types?
  4. How To Convert a Numeric Expression from One Data Type to Another?
  5. How To Convert Numeric Expression Data Types by Assignment Operations?
  6. How To Convert Numeric Expression Data Types using the CAST() Function?
  7. How To Convert Numeric Expression Data Types using the CONVERT() Function?
  8. How To Convert Character Strings into Numeric Values?
  9. What Happens When Converting Big Values to Integers?
  10. What Happens When Converting Big Values to NUMERIC Data Types?
  11. What Are the Mathematical Functions Supported by SQL Server 2005?
  12. How To Convert Numeric Values to Integers?
  13. How To Round a Numeric Value To a Specific Precision?
  14. How To Generate Random Numbers with the RAND() Function?

Related Articles:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...