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:
The TRUNCATE (datetime) function returns date with the time portion of the day truncated to the unit specified by the format model.
A datetime expression that identifies a date and time number.
A text expression that specifies a format model shown in Table 8-13, "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.
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'
When you specify a number as an argument, the TRUNCATE function truncates a number to a specified number of decimal places.
The number to truncate. The value specified for number must be followed by a comma.
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.