Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - FLOOR, CEILING, ROUND - Converting Values to Integers

By: FYIcenter.com

(Continued from previous topic...)

How To Convert Numeric Values to Integers?

Sometimes you need to round a numeric value into an integer. SQL Server 2005 offers you a number of ways to do this:

• FLOOR(value) - Returning the largest integer less than or equal to the input value. The returning data type is the same as the input value.
• CEILLING(value) - Returning the smallest integer greater than or equal to the input value. The returning data type is the same as the input value.
• ROUND(value, 0, 0) - Returning the integer most close to the input value. The returning data type is the same as the input value.
• CAST(value AS INT) - Returning the largest integer less than or equal to the input value. The returning data type is INT.
• CONVERT(INT, value) - Returning the largest integer less than or equal to the input value. The returning data type is INT.

The tutorial exercise below gives some good examples of converting numeric values to integers:

SELECT FLOOR(1234.5678);
SELECT CEILING(1234.5678);
SELECT ROUND(1234.5678, 0, 0);
SELECT CAST(1234.5678 AS INT);
SELECT CONVERT(INT, 1234.5678);
GO
1234
1235
1235.0000
1234
1234

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