SQL FLOOR Function (Transact SQL)

The SQL FLOOR function returns the largest integer that is smaller or equal to the input expression.

Description

The FLOOR function helps for approximating numeric values. It rounds the numeric expression to the value that is the largest number less than or equal to the input number.

SQL FLOOR Usage Notes

The FLOOR function takes one argument. The data type is exact numeric or approximate numeric (like currency). The FLOOR function does not accept the bit data type for the input numeric expression.

The return type is the same as the input numeric expression.

Syntax

FLOOR (input_numeric_expression)

SQL FLOOR Examples

The following SQL query displays results after applying the FLOOR function to positive and negative integer input expressions.

SELECT FLOOR(198.45) Example1, FLOOR(198.50) Example2, FLOOR(-198.50) Example3;
/* Answer */
SELECT FLOOR(198.45) Example1, FLOOR(198.50) Example2, FLOOR(-198.50) Example3;

The positive values approximate 198, and the negative value approximates to -199.

The following example applies the SQL FLOOR function to a currency value.

SELECT FLOOR($125.45) Example1, FLOOR(-$125.45) Example2;
SELECT FLOOR($125.45) Example1, FLOOR(-$125.45) Example2;

The FLOOR function returns 125 for the positive currency and -126 for the negative currency value.

The following examples use the AdventureWorks2019 database.

SELECT FLOOR(AverageRate) AverageRateFloor FROM Sales.CurrencyRate
/* Answer */
SELECT FLOOR(AverageRate) AverageRateFloor
FROM Sales.CurrencyRate

The FLOOR function returns the greatest least integer against each value in the Average Rate column of the Currency Rate table.

To Learn more SQL See Also

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Are YOU READY TO START LEARNING SQL SERVER, BUT DON'T KNOW WHERE TO START?

Nothing is worse than, being excited to learn a new tool but not knowing where to start, wasting time learning the wrong features, and being overwhelmed .

But it doesn't have to be this way.

I'm Putting together a free email course to help you get started learning SQL Server.

Name*
Email*
>