SQL FLOOR() Function


The SQL FLOOR() is a function, and return previous whole integer (no fractional digits) value that is equal to or less than a given input floating number from query result.

The SQL FLOOR() function will always goes to the previous whole integer value.

Ex:

  • Floor(6) = result is "6"
  • Floor(3.77) = result is "3"
  • Floor(5.12) = result is "5"
  • Floor(1.9) = result is "1"

The SQL Floor() function is supports only numeric column or an numeric field based expression.

It can be used in SELECT statement as well in where clause.


SQL FLOOR() Syntax

For SQL SERVER / ORACLE / MY SQL

The basic syntax is for numeric value or numeric expression:


SELECT FLOOR(number);

The below syntax is for a given table numeric column or field from a specific table.


SELECT FLOOR(Column_name) FROM table_name;


SQL FLOOR() Example - Using Expression Or Formula

The following SQL SELECT statement returns the previous whole integer(without fraction digits) value of a given input floating number or expression


SELECT FLOOR(3.8) AS 'Expr';

The result of above query is:

Expr
3

SQL FLOOR() Function More Example

Input Value Result
Floor(5.3) 5
Floor(6.8) 6
Floor(-7.7) -8
Floor((-4 * 1.2) + 3) -2

Sample Database Table - Customer

CID CName Balance City
111 Suresh Babu 128.66 Nasik
222 Suresh Kumar -55.23 Nasik
333 Vinoth Kumar -233.85 Chennai
444 Azagu Varshith 560 Madurai
555 Haris Karthik 673.88 Bangalore
666 Kishor Kumar -188.63 Chennai

SQL FLOOR() Example - With Table Column

The following SQL SELECT statement find the previous whole integer(without fraction digits) value of a given table column "Balance" from the "Customer" table:


SELECT CName, 
FLOOR(Balance) As 'New Balance' 
FROM Customer;

The result of above query is:

CName New Balance
Suresh Babu 128
Suresh Kumar -56
Vinoth Kumar -234
Azagu Varshith 560
Haris Karthik 673
Kishor Kumar -189

SQL FLOOR() Example - Using WHERE Clause

The following SQL SELECT statement find the previous whole integer value of a given input number for creating condition (greater than the given value) on table column "Balance" from the "Customer" table:


SELECT CID, CName, Balance 
FROM Customer
WHERE Balance > FLOOR(128.70);

The result of above query is:

CID CName Balance
111 Suresh Babu 128.66
444 Azagu Varshith 560
555 Haris Karthik 673.88

Note: The Floor(128.70) function will return a value of "128". So now the result set contains and display records or rows which has balance more than "128" on column "Balance" from the "Customer" table.