TRUNC

MySQL

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.4

Explanation

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.

PostgreSQL

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

39

Explanation

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.

Oracle

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.4

Explanation

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.

SQL Server

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.5

Explanation

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.