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:
TRUNC (datetime_exp, [fmt])
A datetime expression that identifies a date and time number.
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
date is truncated to the nearest day.
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:
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'
TRUNC (number, truncvalue)
The number to truncate. The value specified for number must be followed by a comma.
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.
The following statement
SHOW TRUNC (15.79, 1)
returns this value