Oracle PL/SQL/Date Timezone/NEXT DAY

Finding the first Monday in the month of September

SQL>
SQL>
SQL> -- Finding the first Monday in the month of September.
SQL> SELECT TO_CHAR(NEXT_DAY("01-SEP-97","Monday"),"MM/DD/YYYY HH:MM:SS AM") "Next_Day" from DUAL;
Next_Day
----------------------
09/08/1997 12:09:00 AM
SQL>

NEXT_DAY("01-AUG-97","Monday"): Finding the first Monday in the month of August

SQL>
SQL>
SQL> -- Finding the first Monday in the month of August.
SQL> SELECT TO_CHAR(NEXT_DAY("01-AUG-97","Monday"),"MM/DD/YYYY HH:MM:SS AM") "Next_Day" from DUAL;
Next_Day
----------------------
08/04/1997 12:08:00 AM
SQL>
SQL>

NEXT_DAY(SYSDATE,"Monday"): Finding the first Monday after the current date and time

SQL>
SQL>
SQL> -- Finding the first Monday after the current date and time.
SQL> SELECT TO_CHAR(NEXT_DAY(SYSDATE,"Monday"),"MM/DD/YYYY HH:MM:SS AM") "Next_Day" from DUAL;
Next_Day
----------------------
09/04/2006 07:09:24 PM
SQL>

NEXT_DAY: the date of the day of the week following a particular date

SQL> -- NEXT_DAY: the date of the day of the week following a particular date
SQL>
SQL>
SQL> SELECT NEXT_DAY(TO_DATE("15SEP2006","DDMONYYYY"),"Monday")
  2  FROM dual;
NEXT_DAY(
---------
18-SEP-06
SQL>

select next_day( date"2001-09-11", "SUNDAY" )

 
SQL>
SQL> select next_day( date"2001-09-11", "SUNDAY" )
  2  from dual;
NEXT_DAY(
---------
16-SEP-01
1 row selected.
SQL>
SQL> --

select next_day( date"2002-01-01", "FRI" )

 
SQL>
SQL> select next_day( date"2002-01-01", "FRI" )
  2  from dual;
NEXT_DAY(
---------
04-JAN-02
1 row selected.
SQL>
SQL> --

The proper method to find the first Monday in a given month

SQL>
SQL>
SQL>
SQL> -- The proper method to find the first Monday in a given month.
SQL> SELECT TO_CHAR(NEXT_DAY("31-AUG-97","Monday"),"MM/DD/YYYY HH:MM:SS AM") "Next_Day" from DUAL;
Next_Day
----------------------
09/01/1997 12:09:00 AM
SQL>