SQL FLOOR Function

The SQL FLOOR function is a Mathematical Function 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]

For this SQL Server Floor Function demonstration, We are going to use the below-shown data

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]

OUTPUT

ANALYSIS

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.

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

OUTPUT