At times you may need to dynamically determine the first or last day of a month based on the current date or some date in your data. This post provides a few options for deriving the First day of month, and Last day of month. And also, it provides a couple tweaks to get Last day of the next or previous month.
Get the current date (today’s date):
select sysdate from dual;
SYSDATE
16-MAY-22
Get the last day of the current month:
select trunc(last_day(sysdate)) as LastDayOfMonth from dual;
LASTDAYOFMONTH
31-MAY-22
Side note: The last_day() function can also be useful for dynamically determining leap years (that is, whether February has 28 or 29 days)
select
last_day(date ‘2020-02-01’) LastDayOfFeb2020, — leap year
last_day(date ‘2021-02-01’) LastDayOfFeb2021 — not a leap year
from dual;
LASTDAYOFFEB2020 LASTDAYOFFEB2021
29-FEB-20 28-FEB-21
Get the last day of the next month:
select add_months(trunc(last_day(sysdate)), 1) as LastDayOfNextMonth from dual;
LASTDAYOFNEXTMONTH
30-JUN-22
Get the last day of the previous month:
select add_months(trunc(last_day(sysdate)), -1) as LastDayOfPreviousMonth from dual;
LASTDAYOFPREVIOUSMONTH
30-APR-22
Get the first day of the current month:
select trunc(sysdate,’month’) as FirstDayOfMonth from dual;
select trunc(sysdate,’MM’) as FirstDayOfMonth from dual;
FIRSTDAYOFMONTH
01-MAY-22
Get the first day of the next month:
select add_months(trunc(sysdate,’MM’), 1) as FirstDayOfNextMonth from dual;
FIRSTDAYOFNEXTMONTH
01-JUN-22
You can also get the first day of the next month using this:
select trunc(last_day(sysdate)+1) as FirstDayOfNextMonth from dual;
FIRSTDAYOFNEXTMONTH
01-JUN-22
Get the first day of the previous month:
select add_months(trunc(sysdate,’MM’), -1) as FirstDayOfPreviousMonth from dual;
FIRSTDAYOFPREVIOUSMONTH
01-APR-22
Here are 3 resources for getting the first and last day of the month values on a SQL Server database:
Thanks for reading! I hope you found this information useful!
Good luck on your data journey.
The Data Community 