using ceiling function in SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

using ceiling function in SQL

Author

Topic

viguro
Starting Member

12 Posts

Posted - 2008-01-10 : 15:16:04

Hello, I'm trying to use the ceiling function in SQL to round up numbers to the nearest 0.5 just like MS Excel does it:
ceiling(numeric, significance). but in SQL it only takes one argument. is there a SQL function similar to the Ceiling function in Excel?

thanks for the help.

tm
Posting Yak Master

160 Posts

Posted - 2008-01-10 : 15:27:22

Check SQL books on line ~ Round() function

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-01-10 : 17:17:13

I'm not sure if ths is what you are looking for, but maybe this might help [url]http://sqlblogcasts.com/blogs/rob_farley/articles/829.aspx[/url]

viguro
Starting Member

12 Posts

Posted - 2008-01-11 : 15:58:04

Thanks guys for the help. what I'm trying to do is as follows:
for example round up this numbers to those:

ceiling(4.124, 0.5) = 4.5
ceiling(7.602, 0.5) = 8.0
ceiling(3.023, 0.5) = 3.5

this is how the function works in excel, but in SQL it only takes one argument and doesn't allows the precision argument.

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-11 : 16:29:04

This will round to the next .5:

select floor(<number>) + ((cast(<number>*10 as int)/5 % 2)+1)*.5

example:

declare @i float
set @i = 3.023
select floor(@i) + ((cast(@i*10 as int)/5 % 2)+1)*.5

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-12 : 01:08:25

quote:
Originally posted by jdaman

This will round to the next .5:

select floor(<number>) + ((cast(<number>*10 as int)/5 % 2)+1)*.5

example:

declare @i float
set @i = 3.023
select floor(@i) + ((cast(@i*10 as int)/5 % 2)+1)*.5

Other method

declare @i float
set @i = 3.023
select (floor(@i+0.5)+ceiling(@i))/2.0

Madhivanan

Failing to plan is Planning to fail

ceegh
Starting Member

1 Post

Posted - 2008-04-16 : 12:22:33

quote:
Originally posted by madhivanan

quote:
Originally posted by jdaman

This will round to the next .5:

select floor(<number>) + ((cast(<number>*10 as int)/5 % 2)+1)*.5

example:

declare @i float
set @i = 3.023
select floor(@i) + ((cast(@i*10 as int)/5 % 2)+1)*.5

Other method

declare @i float
set @i = 3.023
select (floor(@i+0.5)+ceiling(@i))/2.0

Madhivanan

Failing to plan is Planning to fail

simpler still, with the Excel version being CEILING(num, sig):

select @sig * ceiling(@num/@sig)

the floor version is what you might expect:

select @sig * floor(@num/@sig)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-18 : 03:01:16

quote:
Originally posted by ceegh

quote:
Originally posted by madhivanan

quote:
Originally posted by jdaman

This will round to the next .5:

select floor(<number>) + ((cast(<number>*10 as int)/5 % 2)+1)*.5

example:

declare @i float
set @i = 3.023
select floor(@i) + ((cast(@i*10 as int)/5 % 2)+1)*.5

Other method

declare @i float
set @i = 3.023
select (floor(@i+0.5)+ceiling(@i))/2.0

Madhivanan

Failing to plan is Planning to fail

simpler still, with the Excel version being CEILING(num, sig):

select @sig * ceiling(@num/@sig)

the floor version is what you might expect:

select @sig * floor(@num/@sig)

Is your floor method correct?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources