Oracle NEXT_DAY and LAST_DAY Function Guide, FAQ, and Examples

The Oracle NEXT_DAY and LAST_DAY functions are similar functions used to determine dates. Learn more about what they can do and their differences in this article.

Purpose of NEXT_DAY and LAST_DAY Functions

The Oracle NEXT_DAY function returns the date of the first weekday that comes after the specified date value. It’s good for working with employee working dates or anything that needs a workday as part of its logic.

The LAST_DAY function returns a date value equal to the last day of the month of a specified date value.

It’s not always guaranteed to find the last day of a month by just specifying day 30, because some months have 31 days, or even 28 or 29, as I’m sure you know.

Rather than use a complex function or a database table to find out the last day of the month, LAST_DAY does it easily.

 

NEXT_DAY Syntax and Parameters

The syntax for the Oracle NEXT_DAY function is:

NEXT_DAY ( input_date, weekday )

The parameters of the NEXT_DAY function are:

• input_date (mandatory): This is the date used to find the next weekday. The function returns the weekday that occurs after this date.
• weekday (mandatory): This is the day of the week that you want to return. It’s a character data type, so it needs to be enclosed in quotes.

Some other things to note about this function are:

• The return type is always a date value, regardless of the date type that was provided as input_date.
• For the weekday parameter, you can also specify abbreviated names of the week.
• The full name or abbreviated name of the weekday must be in the date language of your database session.
• The return value has the same values for the hours, minutes, and seconds as the input_date.

 

LAST_DAY Syntax and Parameters

The syntax of the LAST_DAY function is:

LAST_DAY (input_date)

The parameters of the LAST_DAY function are:

• input_date (mandatory): This is the date to find the last day in the month of. It can be any of the Oracle DATE data types (e.g. DATE, TIMESTAMP).

The return type of this function is DATE.

 

Possible Values for the Weekday Parameter in NEXT_DAY

The possible values of the weekday parameter will depend on your database session language, but can be the full names or the abbreviated names.

In English, they are:

Day of the week	Possible Values
Monday	MON, MONDAY
Tuesday	TUE, TUES, TUESDAY
Wednesday	WED, WEDNESDAY
Thursday	THU, THUR, THURS, THURSDAY
Friday	FRI, FRIDAY
Saturday	SAT, SATURDAY
Sunday	SUN, SUNDAY

For the abbreviated names, you need to specify at least the minimum number of characters in the abbreviation. Any more than this and the function will still work.

For example, if you wanted THURSDAY, you can specify THU, THUR, THURS, THURSD, THURSDA, and THURSDAY. They will all work, as they contain the abbreviate form (THU).

 

Can You Find The Last Day Of A Previous Month?

Yes, you can. You can use LAST_DAY in combination with ADD_MONTHS to find the last day of a previous month.

For example, you can use ADD_MONTHS(sysdate, -1) to find the date one month in the past, then use the LAST_DAY function on that.

See the Examples section below for more information.

 

What’s the Difference Between NEXT_DAY and LAST_DAY?

They have similar names but there is a difference.

NEXT_DAY returns the date of the first weekday that comes after the specified date.

LAST_DAY returns the date matching the last day of the month of the specified date.

 

Examples of the Oracle NEXT_DAY and LAST_DAY Function

Here are some examples of the NEXT_DAY and LAST_DAY functions. I find that examples are the best way for me to learn about code, even with the explanation above.

Example 1 – NEXT_DAY and Full Day Name

This example shows the next Wednesday from today’s date.

SELECT SYSDATE,
NEXT_DAY(SYSDATE, 'WEDNESDAY') AS next_date_example
FROM dual;

Result:

SYSDATE	NEXT_DATE_EXAMPLE
26-Nov-17	27-Nov-17

 

Example 2 – NEXT_DAY and Abbreviated Name

This example shows the next Thursday from today’s date, using the abbreviated form. I’ve chosen Thursday as SYSDATE is also on a Thursday , and want to see if it shows today’s date or next Thursday‘s.

SELECT SYSDATE,
NEXT_DAY(SYSDATE, 'THU') AS next_date_example
FROM dual;

Result:

SYSDATE	NEXT_DATE_EXAMPLE
26-Nov-17	30-Nov-17

As you can see, it shows next week’s date. The function returns the date “greater than” the specified date, not “greater than or equal to”.

 

Example 3 – NEXT_DAY with TIMESTAMP

This example uses a TIMESTAMP data type.

SELECT SYSTIMESTAMP,
NEXT_DAY(SYSTIMESTAMP, 'SUN') AS next_date_example
FROM dual;

Result:

SYSTIMESTAMP	NEXT_DATE_EXAMPLE
26-NOV-17 10.22.43.547847 AM -08:00	3-Dec-17

 

Example 4 – NEXT_DAY with Invalid Weekday

This example uses an invalid weekday type.

SELECT SYSDATE,
NEXT_DAY(SYSDATE, 'W') AS next_date_example
FROM dual;

Result:

ORA-01846: not a valid day of the week

 

Example 5 – NEXT_DAY and a Specified Date

This example uses a date that isn’t SYSDATE to see how it can be entered.

SELECT
NEXT_DAY('24-JUN-2017', 'THU') AS next_date_example
FROM dual;

Result:

NEXT_DATE_EXAMPLE

29-Jun-17

 

Example 6 – LAST_DAY with SYSDATE

This example uses LAST_DAY with today’s date using SYSDATE.

SELECT SYSDATE,
LAST_DAY(SYSDATE) AS lastday_test
FROM dual;

Result:

SYSDATE	LASTDAY_TEST
26-Nov-17	30-Nov-17

The result shows Nov 30, which is the last day of the current month (Nov 2017).

 

Example 7 – LAST_DAY with Specified Date

This example uses LAST_DAY with a specified date.

SELECT
LAST_DAY('10-NOV-2017') AS lastday_test
FROM dual;

Result:

LASTDAY_TEST

30-Nov-17

The result is Nov 30, 2017, because the specified date was in Nov 2017.

 

Example 8 – ADD_MONTHS and LAST_DATE in the future

This example uses ADD_MONTHS to add a month value to make it a date in the future.

SELECT SYSDATE,
LAST_DAY(ADD_MONTHS(SYSDATE, 5)) AS lastday_test
FROM dual;

Result:

SYSDATE	LASTDAY_TEST
26-Nov-17	30-Apr-18

The result is 30 Apr 2018. This is because 5 months were added to today’s date, to give 26 Apr 2018, then it was converted to the last day using LAST_DAY.

 

Example 9 – ADD_MONTHS and LAST_DATE in the past

This example uses ADD_MONTHS to add a month value to make it a date in the past.

SELECT SYSDATE,
LAST_DAY(ADD_MONTHS(SYSDATE, -1)) AS lastday_test
FROM dual;

Result

SYSDATE	LASTDAY_TEST
26-Nov-17	31-Oct-17

The result shows 31 Oct 2017, because that’s the last day of the previous month (with the current month being Nov 2017).

 

You can find a full list of Oracle SQL functions here.