Ceiling and Floor doubt

Question

0.00/5 (No votes)

See more:

SQL

declare @value decimal
SET @value = 11.49
SELECT ceiling(@value)

o/p --> 11

SQL

declare @value decimal
SET @value = 11.51
SELECT ceiling(@value)

o/p -->12

------------------------But how this changes as below------------------------------

SQL

declare @value decimal(10,4)
SET @value = 11.51
SELECT ceiling(@value)

o/p-->12

SQL

declare @value decimal(10,4)
SET @value = 11.49
SELECT ceiling(@value)

o/p-->12

----------------------------Floor behaving same like ceiling---------------------------

SQL

declare @value decimal
SET @value = 11.49
SELECT floor(@value)

o/p --> 11

SQL

declare @value decimal
SET @value = 11.51
SELECT floor(@value)

o/p -->12

----------------I get result like this below if i change below like this------------------------

SQL

declare @value decimal(10,4)
SET @value = 11.11
SELECT floor(@value)

o/p-->11

SQL

declare @value decimal(10,4)
SET @value = 11.88
SELECT floor(@value)

o/p-->11

Kindly clear the doubt of floor and ceiling

Posted 17-Jul-13 0:34am

anurag19289

Add a Solution

3 solutions

Solution 2

Hi, see this link:)

http://msdn.microsoft.com/en-us/library/ms190927(v=sql.105).aspx[^]

http://www.mssqltips.com/sqlservertip/1589/sql-server-rounding-functions--round-ceiling-and-floor/[^]

Posted 17-Jul-13 1:13am

berrymaria

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Raja Sekhar S · Accepted Answer · 2013-07-17T01:12:00

That's Because while Declaring the Variable @Value u used Decimal data type, u haven't specified the precision and scale.. so it will consider the default Precision and Scale..
Default Precision is 18 and Scale is 0

SQL

Declare @Value decimal-- is equivalent to Declare @Value Decimal(18,0)

in that case

SQL

DECLARE @value DECIMAL--(18,0)
SET @value = 11.49
Select @Value as Value,ceiling(@value) as CeilingValue,Floor(@Value) as FloorValue

The Output will be:

SQL

Value	 CeilingValue	FloorValue
11        11	         11

Where as if u specify the precision and scale then it will be:

SQL

DECLARE @value DECIMAL(10,4)
SET @value = 11.49
Select @Value as Value,ceiling(@value) as CeilingValue,Floor(@Value) as FloorValue

the Output will be:

SQL

Value	CeilingValue	FloorValue
11.4900	    12	         11

Check this Link:http://msdn.microsoft.com/en-us/library/ms187746.aspx[^]

Aarti Meswania · Accepted Answer · 2013-07-17T01:18:00

Run Query and check output hope it will clear doubts.

SQL

declare @value decimal --NOTE you have declared decimal type but not set precision. so val. will be rounded.
SET @value = 11.51 --it's taking 12 as i/p.
--if you pass 11.49, it will auto apply rounding and original val will be 11. just because no precision limit is specified in declaration.
SELECT @value as Original_val, ceiling(@value) as ceiling_val,floor(@value) as floor_val

declare @val decimal(10,4)
SET @val = 11.49
SELECT @val as Original_val, ceiling(@val) as ceiling_val,floor(@val) as floor_val  
SET @val = 11.51
SELECT @val as Original_val, ceiling(@val) as ceiling_val,floor(@val) as floor_val

Happy Coding!
:)

Ceiling and Floor doubt

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0