SQL FLOOR FUNCTION 1

SQL FLOOR Function

The SQL Server 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 Floor Function is:

SELECT FLOOR (Numeric_Expression)
FROM [Source]

SQL Server FLOOR Function Example

The 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]
FLOOR Example 1

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 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 the FLOOR Function.

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

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