trunc usage in oracle

TRUNC function truncates the specified part of the number or date and retains the required part. The usage is TRUNC(expr, fmt), where: expr is the number or date expression to be truncated, and fmt is the specified part to be truncated. . Example: truncate the date to the month and return the first day of the month; truncate the number to an integer and return the number without the decimal part.

TRUNC function in Oracle

The TRUNC function is used to truncate the specified part of the specified number or date, thereby retaining only required parts.

Usage:

<code>TRUNC(expr, fmt)</code>

Where:

• expr: The number or date expression to be truncated.

• fmt: Specify the part to be truncated, which can be the following values:

  • 'YYYYMM': Year and month
  • 'YYYYMMDD': year, month and day
  • 'YYYYMMDDHH24': year, month, day and hour (24-hour format)
  • 'YYYYMMDDHH24MI': year, month, day, hour and minute
  • 'YYYYMMDDHH24MISS': year, month, day, hour, minute and Seconds

Example:

Truncation date:

<code>SELECT TRUNC(sysdate, 'YYYYMM') FROM dual;</code>

This query will return The first day of the month in which the current date falls, that is, the date in the format 'YYYY-MM-01'.

Truncate numbers:

<code>SELECT TRUNC(123.45, 0) FROM dual;</code>

This query will return 123 because fmt being 0 means truncating all decimal places.

Note:

• The TRUNC function only truncates, not rounds.
• When truncating a date, if the specified part is incomplete (for example, the date is truncated to months, but the date is February 29th), an error will be returned.
• If fmt is empty or invalid, the TRUNC function will return an error.

The above is the detailed content of trunc usage in oracle. For more information, please follow other related articles on the PHP Chinese website!