TRUNC

The TRUNC function truncates either a number or a date and time value. Because the syntax of the TRUNC function is different depending on the whether it is being used for a number or a date and time value, two separate entries are provided:

TRUNC (for dates and time)
TRUNC (for numbers)

TRUNC (for dates and time)

When you specify a date and time value as an argument, the TRUNC function returns the date and time value truncated to a specified date format. When you do not specify a format, the date and time value is truncated to the nearest day.

Return Value

DATETIME

Syntax

TRUNC (datetime_exp, fmt)

Arguments

datetime-exp

An expression that identifies a date and time number.

fmt

A text expression that specifies one of the format models shown in Table 24-4, "Format Models for TRUNC for Dates and Time". A format model indicates how the date and time number should be truncated.

Table 24-4 Format Models for TRUNC for Dates and Time

Format Model Description

Format Model	Description
CC SCC	One greater than the first two digits of a 4-digit year to indicate the next century. For example, 1900 becomes 2000. `S` prefixes BC dates with `-`.
D DAY DY	Starting day of the week (1 to 7). The day of the week that is number 1 is controlled by NLS_TERRITORY (See NLS Options).
DD	Day of month

SCC

One greater than the first two digits of a 4-digit year to indicate the next century. For example, 1900 becomes 2000. S prefixes BC dates with -.

DAY

Starting day of the week (1 to 7). The day of the week that is number 1 is controlled by NLS_TERRITORY (See NLS Options).

Day of month

Examples

Example 24-18 Truncating to the Nearest Year

When the value of the NLS_DATE_FORMAT option is DD-MON-YY, then this statement:

SHOW TRUNC ('27-OCT-92','YEAR')

returns this value:

01-JAN-92

TRUNC (for numbers)

When you specify a number as an argument, the TRUNC function truncates a number to a specified number of decimal places.

Return Value

DECIMAL

Syntax

TRUNC (number, truncvalue)

Arguments

number: The number to truncate. The value specified for number must be followed by a comma.
truncvalue: An INTEGER value that specifies the number of places to the right or left of the decimal point to which number should be truncated. When truncvalue is positive, digits to the right of the decimal point are truncated. When it is negative, digits to the left of the decimal point are truncated (that is, made zero). When truncvalue is omitted, number is truncated to 0 decimal places.

Examples

Example 24-19 Truncating to the Right of the Decimal Point

The following statement

SHOW TRUNC (15.79, 1)

returns this value

15.7

Example 24-20 Truncating to the Left of the Decimal Point

The following statement

SHOW TRUNC (15.79, -1)

returns this value