2008-11-24
Among various helpful functions provided by Oracle, Trunc function took my attention today because I had to fix a defect where some one has misunderstood and misused this function.
As the name itself suggests, the trunc function is capable of truncating a value. This function can be used either on a date type value or a numeric value.
Using trunc for numeric values
When it comes to numbers, trunc function can return a number truncated to a certain number of decimal places.
syntax:
trunc( number, [ decimal_places ] )
Example usage:
| trunc(125.815) | would return 125 |
| trunc(125.815, 0) | would return 125 |
| trunc(125.815, 1) | would return 125.8 |
| trunc(125.815, 2) | would return 125.81 |
| trunc(125.815, 3) | would return 125.815 |
| trunc(-125.815, 2) | would return -125.81 |
| trunc(125.815, -1) | would return 120 |
| trunc(125.815, -2) | would return 100 |
| trunc(125.815, -3) | would return 0 |
Menol
Note: The trunc function does not round the values like in round function. It simply truncates the number as instructed.
Using trunc for date values
The trunc function is capable to truncate a date value to a specific unit of measure.
Syntax:
trunc ( date, [ format ] )
Possible values for format parameter:
| Unit | Valid format parameters |
|---|---|
| Year | SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y |
| ISO Year | IYYY, IY, I |
| Quarter | Q |
| Month | MONTH, MON, MM, RM |
| Week | WW |
| IW | IW |
| W | W |
| Day | DDD, DD, J |
| Start day of the week | DAY, DY, D |
| Hour | HH, HH12, HH24 |
| Minute | MI |
Menol
Example Usage:
| trunc(to_date(’22-AUG-03′), ‘YEAR’) | would return ’01-JAN-03′ |
| trunc(to_date(’22-AUG-03′), ‘Q’) | would return ’01-JUL-03′ |
| trunc(to_date(’22-AUG-03′), ‘MONTH’) | would return ’01-AUG-03′ |
| trunc(to_date(’22-AUG-03′), ‘DDD’) | would return ’22-AUG-03′ |
| trunc(to_date(’22-AUG-03′), ‘DAY’) | would return ’17-AUG-03′ |
Menol
Was this post helpful to you? How can I improve? – Your comment is highly appreciated!
Cassian Menol Razeek
