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.
And this function is Supported in Oracle 11g, Oracle 12c, Oracle 10g, Oracle 9i.
Example –
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'
Syntax –
NEXT_DAY(DATE,WEEKDAY)
Parameters :
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;
Output –
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;
Output –
Example 3 –
Using SYSDATE
SELECT NEXT_DAY(SYSDATE,'MONDAY') AS NEXT_MONDAY FROM DUAL;
Output –