Tutorial :Calculate a date in Oracle SQL



Question:

I want to substract exactly 6 month from a given date. How do you do this in Oracle SQL?


Solution:1

SELECT ADD_MONTHS(SYSDATE, -6) FROM dual  

See ADD_MONTHS().


Solution:2

You write "I want to substract exactly 6 month from a given date". But what is "exactly 6 month" exactly? It's not trivial. For example, let's take 30th August, 2009. What date is exactly 6 months earlier? Or 28th February, 2009...

So you have to define what you mean and then decide which method you want to use:

  • add_months(...,-6)
  • -interval '6' month
  • own code

An example:

SQL> select add_months(date '2009-08-30', -6)    2       , add_months(date '2009-02-28', -6)    3    from dual    4  /    ADD_MONTHS(DATE'200 ADD_MONTHS(DATE'200  ------------------- -------------------  28-02-2009 00:00:00 31-08-2008 00:00:00    1 row selected.    SQL> select date '2009-02-28' - interval '6' month    2    from dual    3  /    DATE'2009-02-28'-IN  -------------------  28-08-2008 00:00:00    1 row selected.    SQL> select date '2009-08-30' - interval '6' month    2    from dual    3  /  select date '2009-08-30' - interval '6' month                           *  ERROR at line 1:  ORA-01839: date not valid for month specified  

As you can see, there is a clear difference between add_months and the interval notation.

Regards, Rob.


Solution:3

add_months(..., -6)


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »