Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - Arithmetic Operations with Different Data Types

By: FYIcenter.com

(Continued from previous topic...)

What Happens to an Arithmetic Operation with Two Different Data Types?

When two expressions with different data types are put together for an arithmetic operation, the expression with a lower rank data type will be converted automatically to higher rank data type. Usually, a data type with a larger storage size has a higher rank. A conversion from a lower rank data type to a higher rank data type is considered safe, because there is no data lose during the conversion.

The tutorial exercise shows you some good examples on automatic conversion during arithmetic operations:

-- INT / INT, No conversion
-- Returning INT
DECLARE @i INT;
SET @i = 1;
SELECT @i/3;
GO
0

-- NUMERIC / INT, INT converted to NUMERIC
-- Returning NUMERIC
DECLARE @d NUMERIC(9,6);
SET @d = 1.0;
SELECT @d/3;
GO
0.33333333

-- INT * NUMERIC, INT converted to NUMERIC
-- Returning NUMERIC
DECLARE @pi NUMERIC(15,7);
DECLARE @d INT;
SET @pi = 3.1415927;
SET @d = 10000000;
SELECT @d*@pi;
GO
31415927.0000000

-- INT * FLOAT, INT converted to FLOAT
-- Returning FLOAT
DECLARE @pi FLOAT(24);
DECLARE @d INT;
SET @pi = 3.1415927E+00;
SET @d = 10000000;
SELECT @d*@pi; -- @d converted from INT to FLOAT
GO
3.141593E+07

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