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

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 [SQLFLOOR]

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

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

The 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 following Mathematical Function statement, We used the function to find the closest integer value of the variable @i (It means FLOOR(-208.45)). We also assigned a new name using the ALIAS Column name.

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

In the last line of the above example, we used it directly on the multiple values.

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

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 below table data

SQL Floor FUNCTION 1
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