CODEWITHSUNDEEP
sqloracle-database
billigzobel
billigzobel

Reputation:

Calculate a date in Oracle SQL

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

Upvotes: 1

Views: 3536

Answers (3)

Rob van Wijk
Rob van Wijk

Reputation: 17705

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.

Upvotes: 4

tuinstoel
tuinstoel

Reputation: 7306

add_months(..., -6)

Upvotes: 1

cletus
cletus

Reputation: 625007

SELECT ADD_MONTHS(SYSDATE, -6) FROM dual

See ADD_MONTHS().

Upvotes: 6

Related Questions