Skip to content
Related Articles

Related Articles

Improve Article

Inbuilt NEXT_DAY Function in PLSQL

  • Difficulty Level : Hard
  • Last Updated : 03 Aug, 2021

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.

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

SUNDAYThe first Sunday later than a date
MONDAYThe first Monday later than a date
TUESDAYThe first Tuesday later than a date
WEDNESDAYThe first Wednesday later than a date
THURSDAYThe first Thursday later than a date
FRIDAYThe first Friday later than a date
SATURDAYThe 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 –

My Personal Notes arrow_drop_up
Recommended Articles
Page :