Previous
Previous
 
Next
Next


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

Parameters

datetime-exp

A datetime expression that identifies a date and time number.

fmt

A text expression that specifies a format model shown in Table: 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

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

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'