Tutorial Gateway

SQL FLOOR Function

by

The SQL FLOOR function is one of the Mathematical Function which is used to return the closest integer value which is less than or equal to the specified expression or Value.

SQL Server FLOOR Function Syntax

The basic syntax of the SQL Server Floor Function is:

SELECT FLOOR (Numeric_Expression)
FROM [Source]

In this article we will show you, How to write FLOOR Function with example. For this, We are going to use the below shown data

SQL Server FLOOR FUNCTION

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 FLOOR function.

T-SQL CODE

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

SQL FLOOR FUNCTION 1

ANALYSIS

Below lines of code is used to declare float variable and assigning the value.

DECLARE @i float
SET @i = -208.45

In the below statement, We used FLOOR function to find the closest integer value of the variable @i (It means FLOOR(-208.45)). We also assigned new name to that result using SQL ALIAS Column.

SELECT FLOOR(@i)AS [SQL FLOOR]

In the next statement, We used the Sql Server 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

SQL FLOOR Function Example 2

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

T-SQL CODE

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

OUTPUT

SQL FLOOR FUNCTION 2

Thank You for Visiting Our Blog

Placed Under: SQL