Oracle PL/SQL Tutorial/Date Timestamp Functions/Date Arithmetic

Second Tuesday of the Month

SQL>
SQL> CREATE TABLE monthdates(monthdate date NOT NULL PRIMARY KEY);
Table created.
SQL> INSERT INTO monthdates(monthdate) VALUES (DATE "2007-04-01");
1 row created.
SQL> INSERT INTO monthdates(monthdate) VALUES (DATE "2007-05-01");
1 row created.
SQL> INSERT INTO monthdates(monthdate) VALUES (DATE "2007-06-01");
1 row created.
SQL>
SQL> SELECT monthdate    AS first_day_of_month
  2       , monthdate
  3            + MOD( ( 10 - TO_CHAR(monthdate,"d") ), 7 ) + 7
  4                      AS second_tuesday_of_month
  5    FROM monthdates
  6
SQL> DROP TABLE monthdates;
Table dropped.
SQL>

select date "1996-01-29" + interval "1" month as col_1

SQL>
SQL>
SQL> select date "1996-01-29" + interval "1" month as col_1
  2  from   dual;
COL_1
---------
29-FEB-96

select date "1997-08-11" - interval "3" month as col_3

SQL>
SQL>
SQL> select date "1997-08-11" - interval "3" month as col_3
  2  from   dual;
COL_3
---------
11-MAY-97
SQL>

Subtract one date from another, yielding the number of days between the two dates.

The following example subtracts July 31, 2003, from August 2, 2003:
SQL> SELECT TO_DATE("02-AUG-2003") - TO_DATE("31-JUL-2003") FROM dual;
TO_DATE("02-AUG-2003")-TO_DATE("31-JUL-2003")
---------------------------------------------
                                            2
SQL>

Subtracts two days from August 2, 2003

SQL>
SQL>
SQL> SELECT TO_DATE("02-AUG-2003") - 2 FROM dual;
TO_DATE("
---------
31-JUL-03
SQL>

Using Date Arithmetic

You can use the addition and subtraction operators with date type value.

You can add a number, representing a number of days, to a date.

The following example adds 2 days to July 31, 2003, and displays the resulting date:

SQL> SELECT TO_DATE("31-JUL-2003") + 2 FROM dual;
TO_DATE("
---------
02-AUG-03
SQL>