SQL FLOOR Function

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

SELECT FLOOR (Numeric_Expression)
FROM [Source]

SQL Server FLOOR Function Example

The FLOOR Function returns the closest integer value, which is less than or equal to the 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]

The below lines of code are used to declare the SQL Server float variable and assign 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 SQL 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

FLOOR Example 2

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

For this Floor Function demonstration, we will use the table data below.

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