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?

Answer 1

SELECT ADD_MONTHS(SYSDATE, -6) FROM dual

See ADD_MONTHS().

Answer 2

add_months(..., -6)

Answer 3

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.