Skip Headers
Oracle® OLAP DML Reference
11g Release 1 (11.1)

Part Number B28126-01
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

TRUNCATE

The TRUNCATE function (abbreviated as TRUNC) truncates either a number or a datetime 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:


TRUNCATE (datetime)

The TRUNCATE (datetime) function returns date with the time portion of the day truncated to the unit specified by the format model.

Return Value

DATETIME

Syntax

TRUNC (datetime_exp, [fmt])

Arguments

datetime-exp

A datetime expression that identifies a date and time number.

fmt

A text expression that specifies one of the format models shown in Table 8-6, "Datetime Format Templates for the ROUND and TRUNC Date Functions". A format model indicates how the date and time number should be truncated. If you omit fmt, then date is truncated to the nearest day.

Examples

Example 8-152 Truncating to the Nearest Year

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

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

returns this value:

01-JAN-92

Example 8-153 Truncating Using Different Formats

Assume the following option values, variables, and values are in your analytic workspace.

SHOW NLS_DATE_FORMAT
DD-MON-RR
DEFINE MYDATETIME VARIABLE DATETIME
DATE_FORMAT = 'MON-RRRR-DD-HH24'
mydatetime = CURRENT_TIMESTAMP
SHOW mydatetime
= 'AUG-2006-07-14'

As the following SHOW statements illustrate, the value you specify for the format argument of TRUNCATE function determines the value returned by that function.

SHOW TRUNCATE(mydatetime, 'MON')
01-AUG-06
SHOW TRUNCATE(mydatetime, 'DD')
07-AUG-06
SHOW TRUNCATE(mydatetime)
= 'AUG-2006-07-00'

TRUNCATE (number)

When you specify a number as an argument, the TRUNCATE 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 8-154 Truncating to the Right of the Decimal Point

The following statement

SHOW TRUNC (15.79, 1)

returns this value

15.7

Example 8-155 Truncating to the Left of the Decimal Point

The following statement

SHOW TRUNC (15.79, -1)

returns this value

10