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)

• TRUNCATE (number)

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

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

01-JAN-92

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'

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 (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

SHOW TRUNC (15.79, 1)

15.7

SHOW TRUNC (15.79, -1)

10