SQL Server FLOOR()

SQL Server FLOOR() Function

The SQL Server FLOOR() function returns the largest integer less than or equal to a specified number. This function is useful for rounding down numbers to the nearest integer.

---

Syntax

SELECT FLOOR(number);

The FLOOR() function takes a single argument:

• number: The numeric expression to be rounded down.

---

Example SQL Server FLOOR() Function Queries

Let's look at some examples of SQL Server FLOOR() function queries:

1. Basic FLOOR() Example

SELECT FLOOR(4.8) AS result;

This query returns the largest integer less than or equal to 4.8. The result will be:

result
------
4

2. FLOOR() with a Negative Number

SELECT FLOOR(-4.2) AS result;

This query returns the largest integer less than or equal to -4.2. The result will be:

result
------
-5

3. FLOOR() with a Column

SELECT price, FLOOR(price) AS floor_price
FROM products;

This query returns the largest integer less than or equal to the price column for each product in the products table. The result will show the original price and its corresponding floor_price.

4. FLOOR() with a Variable

DECLARE @myNumber FLOAT;
SET @myNumber = 123.456;
SELECT FLOOR(@myNumber) AS result;

This query uses a variable to store a numeric value and then returns its floor value. The result will be:

result
------
123

---

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the FLOOR() function.

Step 1: Creating a Table

This step involves creating a new table named example_table to store some sample data.

CREATE TABLE example_table (
    id INT PRIMARY KEY,
    price DECIMAL(10, 2)
);

In this example, we create a table named example_table with columns for id and price.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the example_table.

INSERT INTO example_table (id, price) VALUES (1, 4.8);
INSERT INTO example_table (id, price) VALUES (2, -4.2);
INSERT INTO example_table (id, price) VALUES (3, 123.456);

Here, we insert data into the example_table.

Step 3: Using the FLOOR() Function

This step involves using the FLOOR() function to return the floor value of the price column.

SELECT id, price, FLOOR(price) AS floor_price
FROM example_table;

This query retrieves the id, price, and the floor value of the price column for each row in the example_table. The result will be:

id  price   floor_price
--- ------- -----------
1   4.8     4
2   -4.2    -5
3   123.456 123

---

Conclusion

The SQL Server FLOOR() function is a powerful tool for returning the largest integer less than or equal to a specified number. Understanding how to use the FLOOR() function and its syntax is essential for effective numeric calculations and data processing in SQL Server.