SQL Server FLOOR() Function

In SQL Server, the FLOOR() function returns the nearest integer that is less than or equal to the specified number or numeric expression.

The FLOOR() function is mainly used to round the floating-point value without fraction part from the floating point number value (decimal, double, float).

FLOOR(number)

Parameters

number: A valid numeric expression. The value of bit type is invalid.

Return Value

Returns the same type as the input expression.

Example 1:

The following example uses the FLOOR() function on the positive and negative numeric value.

SELECT FLOOR (23.34) AS PosInt, FLOOR (-23.34) AS NegInt;

In the above example, FLOOR(23.34) returns 23 because it is nearest and smallest integer value than 23.34. In the same way, FLOOR(-23.34) returns -24 because -24 is smaller than -23.34.

Example 2:

In the following example, zero and input less than one are passed as parameters to the FLOOR function.

SELECT FLOOR(0.01) AS PosInt, FLOOR(0.00) AS Result;

Example 3:

You can pass the numeric expression, as shown below.

SELECT FLOOR(1.6 * 2) AS Result;

Example 4:

The following example demonstrates the difference between the FLOOR() and the CEILING() functions.

SELECT FLOOR(23.34) AS FloorResult, CEILING(23.34) AS CeilingResult;

The following table lists difference between the CEILING() and the FLOOR() function for different input value: