Which function is used to return the largest integer less than or equal to a decimal value?
Developers deal with numerous data types on a day- to-day basis. We need to change the data type or format as per the user requirement. We use ‘SQL Server rounding function’ like SQL Round, Ceiling and Floor to round the values to the nearest numbers. We perform an arithmetic calculation on data as well. It is a challenging task to change the value of a number to an approximate number. We do not want to display decimal numbers in the application front end. Show
The output of the aforementioned round functions depends upon the data types as well. Let’s have a look at each SQL Server Rounding functions definitions in this article. In SQL Server, Round function round a number to a specified length or precision. The SQL Round function accepts three parameters as per the following syntax:
SQL Server Rounding function – CEILING()We use the SQL CEILING function to evaluate the value and return the smallest integer greater than, or equal to, the specified numeric expression. It only accepts one value. Syntax of SQL CEILING function:
SQL Server Rounding function – FLOOR()The SQL Floor function is similar to a CEILING function with one difference. It returns the largest smallest integer greater than, or equal to, the specified numeric expression. It also accepts one value. Syntax of SQL FLOOR function:
Let’s walk through SQL Server Rounding functions with examples in the next section. Example 1: SQL Server Rounding functions with Integer data typeIn this example, we define a variable to hold integer value and use SQL Server Rounding functions to view the output. Positive Integer value
In the output, we can see that all three SQL Rounding functions (Round, CEILING and Floor) return the same output for the positive integer value. We do not have any decimal digit; therefore, Round function does not round the value. Similarly, CEILING and FLOOR function also do not return the smallest or largest integer value.
Negative Integer valueLet’s use negative integer value in the previous example and see the difference in the output.
We also get similar output for the negative integer value as well.
Positive Integer Value with Positive or Negative LengthIn the round function, we can use both positive and negative numbers for the second parameter Length. Let’s use different values in the Length parameter for the round function.
In the output, we can see the following.
Let’s look at another example of three digit positive integer number. It rounds the value until negative length 3 to the nearest tens place. If we use negative length 4 with a three-digit number, it returns 0.
Let’s use another example with integer value 457.
In this example, we can note the following regards to nearest tens place.
Negative Integer Value with Negative LengthIn this example, let’s use a negative length integer value along with the negative length as well. It also rounds up and down the value as per the length.
Example 2: SQL Rounding functions with decimal data typeIn this example, let’s use a variable with decimal data type and check the output for different length precision. Decimal data type value with positive LengthIn the following query, use the positive length for the decimal data type.
In this example, we can see that with decimal values round up to the nearest value as per the length.
Decimal data type value with negative LengthIn the following query, use the negative length for the decimal data type.
We can see that the output is rounded to nearest value .If length is insufficient, it also return zero value.
Decimal data type value with CEILING and Floor SQL Server rounding functions
Example 3: SQL Rounding functions with float data typeFloat data type value with positive and negative LengthLet’s use the float data type with positive and negative length values.
In this example, you can see the following things.
We can also understand using the following example.
Float data type value with CEILING and Floor SQL Server rounding functions
Example 4: SQL Rounding functions with Numeric data typeNumeric data type value with positive and negative LengthIn this example, we will use a numeric data type with both the positive and negative length values. It follows the same behavior as of decimal data type.
Numeric data type value with CEILING and Floor SQL Server rounding functions
Example 5: SQL Server rounding function Truncation using the third argumentIn the above examples, we have not used the third argument Function to truncate the result or round the result. By default, it uses value 0 to round the result. If we want to truncate the result, we can specify a value other than 0.
ConclusionIn this article, we explored the SQL Server Rounding functions with different data types. I would suggest reviewing them as per your environment. If you have any comments or questions, feel free to leave them in the comments below.
Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience. I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines. I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups. Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020. Personal Blog:
https://www.dbblogger.com View all posts by Rajendra Gupta Which numeric function returns the largest integer less than or equal to value?The FLOOR() function returns the largest integer value that is smaller than or equal to a number.
Which number function returns the largest integer value that is equal to or less than a number Trunc () MOD () Celi () floor ()?ceil() The Math. ceil() function always rounds up and returns the smaller integer greater than or equal to a given number.
Which function returns the largest integer value?MAX will return the largest value in a given list of arguments. From a given set of numeric values, it will return the highest value. Unlike MAXA function, the MAX function will count numbers but ignore empty cells, text, the logical values TRUE and FALSE, and text values.
What is the greatest integer less than or equal to?The greatest integer less than or equal to a number x is represented as ⌊x⌋. We will round off the given number to the nearest integer that is less than or equal to the number itself. Mathematically, the greatest integer function ⌊x⌋ can be defined as follows: ⌊x⌋ = n, where n ≤ x < n + 1 and 'n' is an integer.
|