Oracle Trunc Function

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

4 thoughts on “Oracle Trunc Function

  1. great post, great blog!

    you really explained it very well πŸ™‚
    I just didn’t got in example ‘DAY’ returns 17-AUG-03? what’s the difference between DAY and DDD

    Like

    1. Hi Alavian,

      Thanks for your kind feedback. Appriciate it.

      DDD would format the exact date you provided
      e.g.
      trunc(to_date(’22-AUG-03β€²), β€˜DDD’)
      would return ’22-AUG-03β€²
      This is the exact day only formatted the way you specified.

      On the other hand, DAY would return the FIRST DAY of that particular week.

      e.g.
      When you use following command,
      trunc(to_date(’22-AUG-03β€²), β€˜DAY’)

      Then Oracle would look at the calender to see the week this day (22/08/2003) belongs to and will return the starting day of that week (which is 17th).

      So it would return ’17-AUG-03β€²

      I hope this is clear enough.

      Wish you great future…

      Like

  2. Hi Menol,

    So, trunc(to_date(’22-AUG-03β€²), β€˜DDD’)
    and
    trunc(to_date(’22-AUG-03β€²)).
    Are they not same?

    SELECT TRUNC (TO_DATE (’22-AUG-03′)) WITH_DDD, TRUNC (TO_DATE (’22-AUG-03′), ‘DDD’) WITHOUT_DDD
    FROM DUAL;

    Thank You,
    Abhinav Dixit

    Like

  3. Hi Abhinav,

    The results you get may be the same but the process isn’t.

    When you don’t specify the formatting string, then the formatter will use current culture settings it can find in the database server. These settings can easily be changed by an admin for different purposes.

    If current culture settings in the server are same to what you want then you are fine as long as those culture settings remain same. When someone changes those settings then the result you get will not be the same.

    On the other hand, when you specify the format you can rest assured that the output will be exactly what you want regardless of any cultural changes to the server.

    Thanks.

    Menol

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: