TRUNC
TRUNC in SQL is a function used to truncate a number up to a certain decimal place or to truncate a datetime value to a certain unit. This function allows for precision control in results. The number version only keeps specified number of decimal places, and the date version truncates a date or time to a specified unit.
TRUNC(X, D);
- x: The numeric or date value that is to be truncated or rounded down. It can be a field or an expression that evaluates as a value. If a date is provided, it is truncated to the specified date part.
- d: An optional parameter that defines the number of decimal places to which the number X should be truncated. For dates, D determines the level of precision (e.g., year, month, day, etc.). If left empty, numbers default to 0 decimal places and dates default to the day value.
Example
SELECT TRUNC(123.456, 1);Output
123.4Explanation
The TRUNC function is used to truncate a number to a certain number of decimal places specified as the second argument. In the given example, TRUNC(123.456, 1) truncates the number 123.456 to 1 decimal place, resulting in 123.4.
TRUNC(number, scale)
- number: The numerical value that is to be truncated. It can be an expression which evaluates to a decimal or integer number.
- scale: An integer which represents the number of decimal places to be retained after the truncation. If a positive integer is provided, the number is truncated to the specified number of decimal places. If a negative integer is provided, the number is truncated to the left of the decimal point.
Example
SELECT TRUNC(39.83);Output
39Explanation
In PostgreSQL, the TRUNC function is used to truncate a number to a certain number of decimal places. In the given example, it truncates the number 39.83 to the nearest whole number, thus resulting in the output 39. By default, without specifying the decimal places, TRUNC function will truncate the number to 0 decimal places.
TRUNC(number, decimal_places)
- number: This parameter represents the numeric value that needs to be truncated.
- decimal_places: This parameter specifies the number of decimal places to which the number should be truncated. If the decimal places parameter is negative, the function truncates on the left of the decimal point. If it’s positive, truncation happens on the right. If this parameter is omitted, it defaults to 0, meaning the function will truncate to the nearest whole number.
Example
SELECT TRUNC(123.456, 1) FROM dual;Output
123.4Explanation
In the above SQL example for Oracle, the TRUNC function is used to truncate the number 123.456 to one decimal place, resulting in the output 123.4.
TRUNC(number, decimals)
- number: Specifies the number that will be truncated. This is a mandatory parameter.
- decimals: Defines the precision level used to truncate the specified number. It is optional, and if not provided, the number is truncated to 0 decimals.
Example
SELECT TRUNC(129.59, 1) FROM dual;Output
TRUNC(129.59, 1)-------------------------- 129.5Explanation
The SQL TRUNC function is used to truncate a number to a specific number of decimal places. In this example the TRUNC function truncates the number 129.59 to the first decimal place, resulting in 129.5.