how to use next_day function in oracle plsql

Oracle NEXT_DAY Function is used to return the date of the next occurrence of the weekday from the date passed. SQL NEXT_DAY Function can be used with SQL SYSDATE,with other data types and SQL TO_CHAR Function.

---

Oracle NEXT_DAY Function Syntax

SELECT NEXT_DAY(date,weekday)
FROM table_name;

Here in the above Oracle NEXT_DAY Syntax

• ‘date’ is the date that is passed to the NEXT_DAY function
• ‘weekday’ is any day of the week (e.g. Monday, Tuesday, Wednesday, Thursday, Friday, Saturday or Sunday)

---

Oracle NEXT_DAY Function – Using SQL SYSDATE Example

Oracle NEXT_DAY Function can be used with SQL SYSDATE.

For example, the SQL NEXT_DAY Function query below returns the next occurrence of MONDAY from SYSDATE.

SELECT NEXT_DAY(SYSDATE,'MONDAY')
FROM DUAL;

Will return the date for next MONDAY e.g. “11/5/2012 2:40:59 PM”.
The next day’s date returned will depend upon the SYSDATE value of date.

Oracle NEXT_DAY Function – Using VARCHAR Example

Oracle NEXT_DAY Function can be used with other data types also.

For example, the SQL NEXT_DAY Function query below returns the nearest SUNDAY ahead from ’01-Jan-2012′

SELECT NEXT_DAY('01-Jan-2012','SUNDAY')
FROM DUAL;

Will return “1/8/2012” because the next SUNDAY from ’01-Jan-2012′ is January 8^th, 2012.

Oracle NEXT_DAY Function – Using SQL TO_CHAR Example

Oracle NEXT_DAY Function can also be used with the SQL TO_CHAR Function to format the output.

For example, the SQL NEXT_DAY Function query below uses SQL TO_CHAR Function to format the output.

SELECT TO_CHAR(NEXT_DAY('01-Jan-2012','SUNDAY'),'DD-MON-YYYY')
FROM DUAL;

Will return “08-JAN-2012”.

Note the format of the output is different in example 2 and example 3.