printmedia
asked on
Roundup to the nearest whole number in SQL
Hi all.
Is there a function I can use in a sql view or sql task that is equivalent to roundup.
For example, let's say the record equals 0.25, I would like to round up to 1.
Any ideas?
Is there a function I can use in a sql view or sql task that is equivalent to roundup.
For example, let's say the record equals 0.25, I would like to round up to 1.
Any ideas?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I'm using sql server 2000. I don't think those functions are available.
ASKER
I want it to be like the ROUNDUP() function in Excel. So 0.25 = 1, 1.25 = 2 etc.
Is there no equivevalent in SQL Server 2000?
Is there no equivevalent in SQL Server 2000?
CEILING and FLOOR are available in SQL 2000.
ASKER
You're right dbaduck, I was misspelling the word CEILING... Thanks!
Examples
A. Use ROUND and estimates
This example shows two expressions illustrating that with the ROUND function the last digit is always an estimate.
SELECT ROUND(123.9994, 3), ROUND(123.9995, 3)
GO
Here is the result set:
----------- -----------
123.9990 124.0000
B. Use ROUND and rounding approximations
This example shows rounding and approximations.
Statement Result
SELECT ROUND(123.4545, 2)
123.4500
SELECT ROUND(123.45, -2)
100.00
C. Use ROUND to truncate
This example uses two SELECT statements to demonstrate the difference between rounding and truncation. The first statement rounds the result. The second statement truncates the result.
Statement Result
SELECT ROUND(150.75, 0)
151.00
SELECT ROUND(150.75, 0, 1)
150.00