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:
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|
|Tuesday||TUE, TUES, TUESDAY|
|Thursday||THU, THUR, THURS, THURSDAY|
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.
NEXT_DAY(SYSDATE, ‘WEDNESDAY’) AS next_date_example
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.
NEXT_DAY(SYSDATE, ‘THU’) AS next_date_example
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.
NEXT_DAY(SYSTIMESTAMP, ‘SUN’) AS 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.
NEXT_DAY(SYSDATE, ‘W’) AS next_date_example
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
Example 6 – LAST_DAY with SYSDATE
This example uses LAST_DAY with todays date using SYSDATE.
LAST_DAY(SYSDATE) AS lastday_test
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
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.
LAST_DAY(ADD_MONTHS(SYSDATE, 5)) AS lastday_test
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.
LAST_DAY(ADD_MONTHS(SYSDATE, -1)) AS lastday_test
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.
Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!