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