MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Oracle Interval Data Types

with 5 comments

I saw an interesting post on INTERVAL YEAR TO MONTH while checking things out today. It struck me as odd, so I thought I’d share a similar sample along with my opinion about how it should be done in a PL/SQL block.

The example is a modification of what I found in a forum. You should see immediately that it’s a bit complex and doesn’t really describe what you should do with any months. Naturally, the example only dealt with years.

  lv_interval  INTERVAL YEAR TO MONTH;
  lv_end_day   DATE := '30-APR-2009';
  lv_start_day DATE := '30-APR-1975';
  lv_interval := TO_CHAR(FLOOR((lv_end_day - lv_start_day)/365.25))||'-00';

I suggest that the better way is the following because it allows for months, which are a bit irregular when it comes to divisors.

  lv_interval  INTERVAL YEAR TO MONTH;
  lv_end_day   DATE := '30-APR-2009';
  lv_start_day DATE := '30-JAN-1976';
  lv_interval := TO_CHAR(EXTRACT(YEAR FROM lv_end_day) -
                            EXTRACT(YEAR FROM lv_start_day)) ||'-'||
                   TO_CHAR(EXTRACT(MONTH FROM lv_end_day) -
                             EXTRACT(MONTH FROM lv_start_day));

Let me know if you’ve another alternative that you prefer.

Written by maclochlainn

May 2nd, 2009 at 9:10 pm

Posted in Oracle,pl/sql,sql

5 Responses to 'Oracle Interval Data Types'

Subscribe to comments with RSS or TrackBack to 'Oracle Interval Data Types'.

  1. Hi Michael,

    I found a couple typo’s in your first example:

    1. [lv_internal :=] in line 6 should be [lv_interval :=]
    2. [||’00’] in line 7 should be [||’-00′] (I think).

    Once corrected, the output reads [+34-00] in the first example and [+33-03] in the second.

    I’d take your suggested solution any day because I’m of the “more information” is better perspective*. However, if you’re only solving for year interval I don’t know why you’d need the “TO_YMINTERVAL” function at all (or the “TO_CHAR” for that matter).

    Of course, this is just a snippet of a larger whole I’m sure so there may be good reasons for both.

    Mike Kemp

    7 May 09 at 1:43 pm

  2. You’re right, there was also another transcription error. They’re now fixed and the unnecessary casting function removed. Thanks! Great catches.


    7 May 09 at 8:28 pm

  3. Maybe it’s not hard to use this data type, but I can’t really find any use in it. Some suggestions when it may be helpful?

  4. Good explaination


    11 Apr 12 at 1:07 am

  5. Useful enough.

    ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';
    SELECT sysdate "today (t)"
    , sysdate - INTERVAL '3-0' YEAR TO MONTH "t-3y"
    , sysdate - INTERVAL '3-0' YEAR TO MONTH + 30 "t-(3y-30d)"
    , (sysdate - INTERVAL '3-0' YEAR TO MONTH) - INTERVAL '30 0:0:0' DAY TO SECOND  "t-(3y+30d)"
    FROM dual
    today (t)  t-3y       t-(3y-30d) t-(3y+30d)
    ---------- ---------- ---------- ----------
    2013-03-01 2010-03-01 2010-03-31 2010-01-30


    1 Mar 13 at 5:03 pm

Leave a Reply