How to find first and last date of a month in SQL

How to find first and last date of a month in SQL

Working with dates is fun. In this post we are going to show a method to easily find the first and last date of the current month, previous month, and next month.

Knowledge about this will come in handy if you i.e. want to do a year-over-year sales analysis, or maybe you would like to take the current month’s sales figures and compare them to what is budgeted for next month’s sale.

Depending on how your data is organized or partitioned, there are time you want to use the last date of a month in i.e. a BETWEEN statement in SQL.

Here is a query that selects the first and last date of  a month, for last month, current month, and next month.

SELECT TRUNC (SYSDATE) today                                     -- Last Month,

       TO_DATE (TO_CHAR (ADD_MONTHS (SYSDATE, -1), 'YYYYMM'), 'YYYYMM')

          first_date_last_month,

       TO_DATE (TO_CHAR (ADD_MONTHS (SYSDATE, 0), 'YYYYMM'), 'YYYYMM') - 1

          last_date_last_month                                                   -- Current Month

                              ,

       TO_DATE (TO_CHAR (ADD_MONTHS (SYSDATE, 0), 'YYYYMM'), 'YYYYMM')

          first_date_this_month,

       TO_DATE (TO_CHAR (ADD_MONTHS (SYSDATE, +1), 'YYYYMM'), 'YYYYMM') - 1

          last_date_this_month                                                     -- Next Month

                              ,

       TO_DATE (TO_CHAR (ADD_MONTHS (SYSDATE, +1), 'YYYYMM'), 'YYYYMM')

          first_date_next_month,

       TO_DATE (TO_CHAR (ADD_MONTHS (:P_CURR_BEGIN_DATE, +2), 'YYYYMM'), 'YYYYMM') - 1

          last_date_next_month

  FROM DUAL;