Skip Headers
Oracle® OLAP DML Reference
10g Release 2 (10.2)

Part Number B14346-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

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)

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_expfmt)

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 26-4, "Format Models for TRUNC for Dates and Time". A format model indicates how the date and time number should be truncated.

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

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


Examples

Example 26-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 (numbertruncvalue)

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 26-19 Truncating to the Right of the Decimal Point

The following statement

SHOW TRUNC (15.79, 1)

returns this value

15.7

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

The following statement

SHOW TRUNC (15.79, -1)

returns this value

10