Inbuilt NEXT_DAY Function in PLSQL
Prerequisite : PL/SQL Introduction
PLSQL stands for “Procedural Language extensions to SQL” and is used to transform, update and query data in a database. It is grouped into blocks that contain the declaration and statements. And it is integrated with the oracle database (since version 7). And the main idea behind PLSQL is that it adds some programming constraints that are not available in SQL.
In PLSQL NEXT_DAY Function is used to return the first weekday that is greater than the given date. So this function will take the input from the user that is the date and the weekday and then it will return the date which is greater than the given date according to the weekday.
Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course by GeeksforGeeks.
And this function is Supported in Oracle 11g, Oracle 12c, Oracle 10g, Oracle 9i.
INPUT: NEXT_DAY('22-Jul-21','WEDNESDAY') OUTPUT:'28-Jul-21' INPUT: NEXT_DAY('03-Mar-15','MONDAY') OUTPUT:'09-Mar-15' INPUT: NEXT_DAY('22-Jul-20','SUNDAY') OUTPUT:'26-Jul-20'
DATE – The date value which is used to find the next weekday.
WEEKDAY -It is the day of the week which we want to return.
RETURNS – It returns the first date greater than the given date according to the weekday.
The weekday accepts the following Weekday from the table given below – Weekday Return
SUNDAY The first Sunday later than a date MONDAY The first Monday later than a date TUESDAY The first Tuesday later than a date WEDNESDAY The first Wednesday later than a date THURSDAY The first Thursday later than a date FRIDAY The first Friday later than a date SATURDAY The first Saturday later than a date
Example 1 –
SELECT NEXT_DAY( DATE '2000-01-01', 'SUNDAY' ) as NEXT_DATE FROM dual;
Example 2 –
DECLARE input varchar(25); weekday varchar(25); res varchar2(25); BEGIN input:=&input; weekday:=&weekday; res:=NEXT_DAY(input,weekday); dbms_output.put_line('DATE:'||input); dbms_output.put_line('WEEKDAY:'||weekday); dbms_output.put_line('RESULT:'||res); END;
Example 3 –
SELECT NEXT_DAY(SYSDATE,'MONDAY') AS NEXT_MONDAY FROM DUAL;