Oracle Interval Data Types
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.
Subscribe via RSS