{"id":3710,"date":"2024-04-04T08:37:07","date_gmt":"2024-04-04T01:37:07","guid":{"rendered":"https:\/\/www.sqlservertutorial.net\/?page_id=3710"},"modified":"2024-04-04T13:25:16","modified_gmt":"2024-04-04T06:25:16","slug":"sql-server-floor","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-math-functions\/sql-server-floor\/","title":{"rendered":"SQL Server FLOOR() Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL Server FLOOR()<\/code> function to round a number down to the nearest integer that is less than or equal to the input value.<\/p>\n\n\n\n

SQL Server FLOOR function syntax #<\/a><\/h2>\n\n\n\n

The FLOOR<\/code> function rounds a number down to the nearest integer that is less than or equal to the input value.<\/p>\n\n\n\n

Here’s the syntax of the FLOOR()<\/code> function:<\/p>\n\n\n

FLOOR (numeric_expression)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, the numeric_expression<\/code> is a number or an expression that you want to round down to the nearest integer.<\/p>\n\n\n\n
The FLOOR()<\/code> function returns a number with the type depending on the type of the numeric_expression<\/code>.<\/p>\n\n\n\n
The following table shows the type of the input value and the corresponding type of the return value of the FLOOR()<\/code> function:<\/p>\n\n\n\n
Input type<\/th>Return type<\/th><\/tr><\/thead>
float, <\/strong>real<\/td>float<\/td><\/tr>
decimal(p, s)<\/td>decimal(38, s)<\/td><\/tr>
int, <\/strong>smallint, <\/strong>tinyint<\/td>int<\/td><\/tr>
bigint<\/td>bigint<\/td><\/tr>
money, smallmoney<\/td>money<\/td><\/tr>
bit<\/td>float<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
SQL Server FLOOR() function examples #<\/a><\/h2>\n\n\n\n
Let’s take some examples of using the FLOOR()<\/code> function.<\/p>\n\n\n\n
1) Using SQL Server FLOOR() function with positive numbers #<\/a><\/h3>\n\n\n\n
The following example uses the FLOOR()<\/code> function to round down positive numbers:<\/p>\n\n\n
SELECT<\/span>\n  FLOOR<\/span>(11.51<\/span>) x,\n  FLOOR<\/span>(11.25<\/span>) y;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
x  | y\n---+----<\/span>\n11 | 11<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
2) Using the FLOOR() function with negative numbers #<\/a><\/h3>\n\n\n\n
The following statement uses the FLOOR()<\/code> function to round down negative numbers:<\/p>\n\n\n
SELECT<\/span>\n  FLOOR<\/span>(-11.51<\/span>) x,\n  FLOOR<\/span>(-11.25<\/span>) y;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
x   | y\n----+----<\/span>\n-12 | -12<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
3) Using the FLOOR function with money values #<\/a><\/h3>\n\n\n\n
The following example uses the\u00a0FLOOR()<\/code>\u00a0function to round down a money value:<\/p>\n\n\n
SELECT<\/span>\n  FLOOR<\/span>($100<\/span>\/3<\/span>) amount;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
amount\n-------<\/span>\n33.0000<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
4) Using the FLOOR function with table data #<\/a><\/h3>\n\n\n\n
First, create a table<\/a> called teams to store the team name and total members:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> teams (\n   id<\/span> INT<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\n   name<\/span> VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n   total_member INT<\/span> NOT<\/span> NULL<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
INSERT<\/span> INTO<\/span>\n  teams (name<\/span>, total_member)\nVALUES<\/span>\n  ('A'<\/span>, 12<\/span>),\n  ('B'<\/span>, 11<\/span>),\n  ('C'<\/span>, 15<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, use the FLOOR<\/code> function to distribute 100 items to each team in the teams<\/code> table, ensuring each member receives the same number of items:<\/p>\n\n\n
SELECT<\/span>\n  name<\/span>,\n  total_member,\n  FLOOR<\/span>(100<\/span> \/ total_member) item_count\nFROM<\/span>\n  teams;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
name | total_member | item_count\n-----+--------------+-----------<\/span>\nA    | 12           | 8\nB    | 11           | 9\nC    | 15           | 6\n(3 rows)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
    \n
  • Use the FLOOR()<\/code> function to round a number down to the nearest integer that is less than or equal to the input value.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Second, insert rows<\/a> into the teams<\/code> table:<\/p>\n\n\n