|Oracle® OLAP DML Reference
10g Release 1 (10.1)
Part Number B10339-02
The TRUNC function truncates either a number or a date and time 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:
When you specify a date and time value as an argument, the TRUNC function returns the date and time value truncated to a specified date format. When you do not specify a format, the date and time value is truncated to the nearest day.
TRUNC (datetime_exp, fmt)
An expression that identifies a date and time number.
A text expression that specifies one of the format models shown in Table 24-7, "Format Models for TRUNC for Dates and Time". A format model indicates how the date and time number should be truncated.
Table 24-7 Format Models for TRUNC for Dates and Time
|One greater than the first two digits of a 4-digit year to indicate the next century. For example, 1900 becomes 2000. |
|Starting day of the week (1 to 7). The day of the week that is number 1 is controlled by NLS_TERRITORY (See NLS Options).|
|DD||Day of month|
Example 24-18 Truncating to the Nearest Year
When the value of the NLS_DATE_FORMAT option is
DD-MON-YY, then this statement:
SHOW TRUNC ('27-OCT-92','YEAR')
returns this value:
TRUNC (number, truncvalue)
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.
Example 24-19 Truncating to the Right of the Decimal Point
The following statement
SHOW TRUNC (15.79, 1)
returns this value