SQL FLOOR Function

The SQL FLOOR function is used to return the closest integer value, which is less than or equal to the specified expression or Value. The basic syntax of the SQL Server Floor Function is:

SELECT FLOOR (Numeric_Expression)
FROM [Source]

SQL FLOOR Function Example 1

The SQL Server FLOOR Function is used to return the closest integer value, which is less than or equal to given numeric value. The following query will show multiple ways to use the FLOOR function.

DECLARE @i float
SET @i = -208.45

SELECT FLOOR(@i)AS [SQL FLOOR]

-- Calculating FLOOR directly
SELECT FLOOR(0.24)AS [SQL FLOOR]

-- Calculating FLOOR directly
SELECT FLOOR(2.45 + 7.55 - 14.88)AS [SQL FLOOR]
SQL FLOOR FUNCTION 1

Below lines of code are used to declare SQL Server float variable and assigning the value.

DECLARE @i float
SET @i = -208.45

In the below Mathematical Function statement, We used the FLOOR function to find the closest integer value of the variable @i (It means FLOOR(-208.45)). We also assigned a new name using SQL ALIAS Column.

SELECT FLOOR(@i)AS [SQL FLOOR]

In the next statement, We used the FLOOR Function directly on the float value.

-- Calculating FLOOR directly
SELECT FLOOR(0.24)AS [SQL FLOOR]

In the below statement, We used the FLOOR Function directly on the multiple values.

-- Calculating FLOOR directly
SELECT FLOOR(2.45 + 7.55 - 14.88)AS [SQL FLOOR]

It means FLOOR (2.45 + 7.55 – 14.88) => FLOOR (-4.88) = – 5

FLOOR Function Example 2

We are going to find the closet integer values for all the records present in [Service Grade] using FLOOR Function.

For this SQL Server Floor Function demonstration, we are going to use the Math Table data

SELECT [EnglishProductName]
      ,[Color]
      ,FLOOR([StandardCost]) AS COST
      ,FLOOR([SalesAmount]) AS SALES
      ,FLOOR([TaxAmt]) AS TAX
      ,FLOOR([Service Grade]) AS Grade
  FROM [Mathemetical Functions]
SQL FLOOR FUNCTION 2