NEXT_DAY and LAST_DAY functions in Oracle

NEXT_DAY and LAST_DAY functions

The NEXT_DAY and LAST_DAY functions can be used to calculate for example «the last Saturday in any given month». You can simply get the last day in the month, subtract 7 days from that, and then use NEXT_DAY to find the next Saturday after that one.

NEXT_DAY (date, char)

NEXT_DAY returns the date of the first weekday named by char that is later than date. The return type is always DATE, regardless of the datatype of date. The argument char must be a day of the week in the date language of your session, either the full name or the abbreviation. The minimum number of letters required is the number of letters in the abbreviated version. Any characters immediately following the valid abbreviation are ignored. The return value has the same hours, minutes, and seconds component as the argument date.

Example

Return the date of the next Monday after now:

SELECT TO_CHAR(NEXT_DAY(sysdate,'MON'),'DD.MM.YYYY') "Next Monday from now"
FROM DUAL;

Next Monday
-----------
06.12.2004

LAST_DAY(date)

LAST_DAY returns the date of the last day of the month that contains date. The return type is always DATE, regardless of the datatype of date.

Example

The following statement determines how many days are left in the current month:

SELECT SYSDATE,
LAST_DAY(SYSDATE) "Last",
LAST_DAY(SYSDATE) - SYSDATE "Days Left"
FROM DUAL;

SYSDATE Last Days Left
--------- --------- ----------
03-DEC-04 31-DEC-04 28

Get the last date of a month:

SELECT LAST_DAY (TO_DATE ('02','MM')) FROM dual;

LAST_DAY
---------
29-FEB-04

Return the last Saturday of each month for a given year

You can simply get the last day in the month, subtract 7 days from that, and then use NEXT_DAY to find the next Saturday after that one.

DEFINE my_month = 12;

SELECT TO_CHAR (
NEXT_DAY (
LAST_DAY (TO_DATE (&my_month,'MM' )) - 7,
TO_CHAR (TO_DATE ('29-01-1927', 'DD-MM-YYYY' ),'DAY')
),'DD.MM.YYYY') "Last Saturday in December 2004"
FROM dual;

Last Saturday in December 2004
------------------------------
25.12.2004

Return the last Saturdays for the current year.

SELECT TO_CHAR (
NEXT_DAY (
LAST_DAY (
ADD_MONTHS (TRUNC(SYSDATE,'Y'),ROWNUM-1))-7,
TO_CHAR (TO_DATE('29-01-1927', 'DD-MM-YYYY'),'DAY')
), 'DD.MM.YYYY') "Last Saturdays in 2004"
FROM ALL_OBJECTS
WHERE ROWNUM <= 12;

Last Saturdays in 2004
----------------------
31.01.2004
28.02.2004
27.03.2004
24.04.2004
29.05.2004
26.06.2004
31.07.2004
28.08.2004
25.09.2004
30.10.2004
27.11.2004
25.12.2004

The "29-01-1927" is just a random date that we knew was a Saturday—any Saturday would do. This is done instead of using "SAT" in the query for international reasons, because in languages other than English, "SAT" isn't Saturday. This query should work in any language out there.