15,796,299 members
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

## Solution 1

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```

v5
anurag19289 17-Jul-13 8:33am
Excellent... Thanks for the help got it... :)
Raja Sekhar S 18-Jul-13 0:54am
You are Welcome...

## Solution 3

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!
:)

anurag19289 17-Jul-13 8:34am
Nice lucid explanation... thanks :)
Aarti Meswania 17-Jul-13 9:55am
Welcome!