Trunc Function in SQL
FREE
The TRUNCATE() function will be demonstrated in this article. In PLSQL, the TRUNCATE function is used to truncate an integer to a set of decimal places. If the decimal places parameter is not given, the TRUNC (numbers) function delivers the nearest integer value by default.
TRUNC() is a Date/Time method in Oracle. This method is used to truncate the specified date using the specified unit of measurement.
In this article, we will discuss the syntax and parameters of TRUNC in SQL.
What is the TRUNC Function in SQL?
The function of the TRUNC method in PLSQL is to return a value that has been truncated (shortened) to a specific number of decimal places. The TRUNC (value) method returns number1 decimal places shortened to number2. If number2 is left out, the number1 will be shortened to zero places.
The first parameter is the number to be truncated; the second signifies the number of decimal places. The second parameter can be positive, negative, or zero. A positive number indicates that the decimal point should be truncated to the right side; negative digit values indicate that the decimal point should be truncated to the left side. Let's discuss each of these situations.
- Suppose the number is 2436.8746 and the second parameter is empty.
Output:
- Suppose the number is 2436.8746 and the second parameter is 2.
Output:
- Suppose the number is 2436.8746 and the second parameter is -2.
Output:
- Suppose the number is 2436.8746 and the second parameter is -3.
Output:
Syntax of TRUNC Function in SQL
The syntax for trunc in SQL is as follows:
Syntax:
Parameter of TRUNC Function in SQL
There are two parameters of the trunc function in SQL.
- number -> The first parameter is the number which is the value that needs to be truncated.
- decimal_places -> The second parameter is decimal_places. This signifies the number of decimal places the number needs to be truncated to. The value of this parameter must be of integer type. An optional second input to the TRUNC function sets the precision of the returned value. When the first parameter is a numeric expression, the second argument must be a number in the range -32 to +32 inclusive, specifying the final significant digit of the returned value's location (relative to the decimal point). TRUNC function in SQL is responsible for returning the first argument's value truncated to a scale of 0 or to the unit's place if the factor specification is omitted when the first argument is numeric.
Returns Type of TRUNC Function in SQL
Trunc in SQL returns a number as the output with the provided number of decimal places truncated.
Examples of TRUNC Function in SQL
Here are some examples of how to use the TRUNC function:
Example 1) Trunc function with only one parameter
Output:
Example 2) Trunc function with the parameter decimal place as 0.
Output:
Since 6 does not have a decimal point, the truncated result of (6, 0) is 6, and so the output is 6.
Example 3) Trunc function with the parameter decimal place as 3.
Output:
Because 3 is at the position of the decimal place parameter in the above example, the truncated value of 16.4125 is 16.412, indicating that the resulting value must have 3 decimal numbers after the decimal point.
Example 4: Trunc function with the parameter decimal place as -2.
Output:
Example 5: Trunc function with the parameter number as a negative value and decimal place as 2.
Output:
Conclusion
- The TRUNC function has only two parameters. The TRUNC method is responsible for truncating the integer to 0 decimal places if this parameter is missing.
- The TRUNC function is similar to the round function, except that it truncates (instead of rounding off the closest whole number) any component of the first argument that is less than the least significant digit within the precision specified by the second parameter.