MacLochlainns Weblog

Michael McLaughlin’s Technical Blog

Site Admin

Oracle Interval Data Types

with 2 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.

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

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

DECLARE
  lv_interval  INTERVAL YEAR TO MONTH;
  lv_end_day   DATE := '30-APR-2009';
  lv_start_day DATE := '30-JAN-1976';
BEGIN
  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));
  DBMS_OUTPUT.put_line(lv_interval);
END;
/

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

«
»

2 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.

    maclochlainn

    7 May 09 at 8:28 pm

Leave a Reply