The SQL FLOOR function is used to return the largest integer that is less than or equal to a given numeric expression. In other words, it rounds down the input value to the nearest integer value. The syntax of the FLOOR function is as follows:
where numeric_expression is the value that needs to be rounded down.
The FLOOR function is commonly used in SQL queries that require the rounding of decimal values to integers, especially when working with financial data or calculating percentages. For example, suppose you have a table of sales data that includes a column of total sales values. You could use the FLOOR function to round the sales values down to the nearest whole number, like this:
SELECT FLOOR(total_sales) as rounded_sales FROM sales_data;
In this example, the FLOOR function is used to round down the total_sales column to the nearest whole number, and the result is returned in a new column called rounded_sales.
It’s important to note that the FLOOR function always returns an integer value, regardless of the input data type. If the input value is already an integer, the FLOOR function will simply return the same value without any rounding.
In addition to the FLOOR function, SQL also provides the CEILING function, which rounds up the input value to the nearest integer, and the ROUND function, which rounds the input value to a specified number of decimal places.
In summary, the SQL FLOOR function is a useful tool for rounding down numeric values to the nearest integer value. It can be used in a variety of SQL queries, particularly those involving financial or mathematical calculations.