Second Tuesday of the Month : Date Arithmetic « Date Timestamp Functions « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Date Timestamp Functions
3. Date Arithmetic

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>