Today, I got introduced to a new function in Oracle. This function, TO_YMINTERVAL was introduced from Oracle 9i onwards and it converts a string to INTERVAL YEAR to MONTH type. This can be understood in a better way with some examples.
First, let us get the current date value from the DUAL table. This is only for comparison purposes.
SQL> SELECT SYSDATE FROM DUAL
2 /
SYSDATE
-----------
29/06/2008
Now, when I give '02-02' in TO_YMINTERVAL, it increments the sysdate by 2 years and 2 months.
SQL> SELECT SYSDATE + TO_YMINTERVAL('02-02') FROM DUAL
2 /
SYSDATE+TO_YMINTERVAL('02-02')
------------------------------
29/08/2010 7:01:53 PM
"SYSDATE -" helps in knowing the date 2 years and 2 months before..
SQL> SELECT SYSDATE - TO_YMINTERVAL('02-02') FROM DUAL
2 /
SYSDATE-TO_YMINTERVAL('02-02')
------------------------------
29/04/2006 7:02:15 PM
Arithematic logic. - * + is '-'. It does the same thing as shown below..
SQL> SELECT SYSDATE + TO_YMINTERVAL('-02-02') FROM DUAL
2 /
SYSDATE+TO_YMINTERVAL('-02-02'
------------------------------
29/04/2006 7:02:50 PM
This one is tricky. When I give 'SYSDATE -' and give a negative value in TO_YMINTERVAL it will add the result from the current date. Hence, we end up in the date that is 2 years and 2 months ahead from now.
SQL> SELECT SYSDATE - TO_YMINTERVAL('-02-02') FROM DUAL
2 /
SYSDATE-TO_YMINTERVAL('-02-02'
------------------------------
29/08/2010 7:03:15 PM
First, let us get the current date value from the DUAL table. This is only for comparison purposes.
SQL> SELECT SYSDATE FROM DUAL
2 /
SYSDATE
-----------
29/06/2008
Now, when I give '02-02' in TO_YMINTERVAL, it increments the sysdate by 2 years and 2 months.
SQL> SELECT SYSDATE + TO_YMINTERVAL('02-02') FROM DUAL
2 /
SYSDATE+TO_YMINTERVAL('02-02')
------------------------------
29/08/2010 7:01:53 PM
"SYSDATE -" helps in knowing the date 2 years and 2 months before..
SQL> SELECT SYSDATE - TO_YMINTERVAL('02-02') FROM DUAL
2 /
SYSDATE-TO_YMINTERVAL('02-02')
------------------------------
29/04/2006 7:02:15 PM
Arithematic logic. - * + is '-'. It does the same thing as shown below..
SQL> SELECT SYSDATE + TO_YMINTERVAL('-02-02') FROM DUAL
2 /
SYSDATE+TO_YMINTERVAL('-02-02'
------------------------------
29/04/2006 7:02:50 PM
This one is tricky. When I give 'SYSDATE -' and give a negative value in TO_YMINTERVAL it will add the result from the current date. Hence, we end up in the date that is 2 years and 2 months ahead from now.
SQL> SELECT SYSDATE - TO_YMINTERVAL('-02-02') FROM DUAL
2 /
SYSDATE-TO_YMINTERVAL('-02-02'
------------------------------
29/08/2010 7:03:15 PM
It doesn't work in all cases. Cannot handle Feb of leap year.
ReplyDeleteSQL> select to_date('29-Jan-2001') + to_yminterval('01-01') future_date from dual
2 /
select to_date('29-Jan-2001') + to_yminterval('01-01') future_date from dual
*
ERROR at line 1:
ORA-01839: date not valid for month specified
SQL> select to_date('30-Jan-2001') + to_yminterval('01-01') future_date from dual
2 /
select to_date('30-Jan-2001') + to_yminterval('01-01') future_date from dual
*
ERROR at line 1:
ORA-01839: date not valid for month specified
SQL> select to_date('31-Jan-2001') + to_yminterval('01-01') future_date from dual
2 /
select to_date('31-Jan-2001') + to_yminterval('01-01') future_date from dual
*
ERROR at line 1:
ORA-01839: date not valid for month specified
SQL> select to_date('28-Jan-2001') + to_yminterval('01-01') future_date from dual
2 /
FUTURE_DA
---------
28-FEB-02
@Sandy
ReplyDeleteThe date you are trying to fetch is not valid, right?
What do you think should be the expected output for these examples?
It didn't work for me either for Leap Year cases. I want to show a purchase date and a date one year ago from that date. It fails on 2/29/2008. It can't figure out that one year ago 2/29/2007 is not possible and doesn't show instead 2/28/2007 which will be okay !! ughhhhh.....
ReplyDelete