Syntax: NEXT_DAY (
LAST_DAY (date)
Options are SUN, MON, TUE, WED, THU, FRI, and SAT
I give some examples which will explain how to find the THIS WEEK, Next WEEK and THIS MONTH, NEXT MONTH.
Note:- Assuming SUNDAY - SATURDAY as week
1) Finding this week dates
WHERE pending_date between TO_CHAR (NEXT_DAY(sysdate - 7, 'SUN'), 'dd-Mon-yyyy') and TO_CHAR (NEXT_DAY(sysdate - 1, 'SAT'), 'dd-Mon-yyyy')
2) Finding next week dates
WHERE pending_date between TO_CHAR (NEXT_DAY(sysdate, 'SUN'), 'dd-Mon-yyyy') and TO_CHAR (NEXT_DAY(sysdate + 6, 'SAT'), 'dd-Mon-yyyy')
3) Finding this month dates
TO_CHAR (TRUNC(sysdate, 'MM'), 'dd-Mon-yyyy') and TO_CHAR (LAST_DAY(sysdate), 'dd-Mon-yyyy')
4) Finding next month dates
TO_CHAR (LAST_DAY(sysdate) + 1, 'dd-Mon-yyyy') and TO_CHAR (LAST_DAY(ADD_MONTHS(sysdate)), 'dd-Mon-yyyy')
Quick Points :
1) LAST_DAY returns last day of the month.
2) NEXT_day returns next occurance of the given week in it.
3) TRUNC(sysdate, 'MM') always returns starting date of the month.
4) ADD_MONTHS(sysdate, 2) adds 2 months to current date. e.g: present month is oct then it will become DEC. if you use -2 negative number it will reduce in our case it becomes AUG.
