Get the last day of each month in the last two years using oracle SQL

Question

I am new to Oracle SQL. I know the LAST_DAY function will return the last day of the month given a specific date. But how can I get the last day of each month in the last two years? Thanks so much!

Answer 1

SELECT   TRUNC (ADD_MONTHS (SYSDATE, -(LEVEL - 1)), 'MM') FIRST_DAY,
             LAST_DAY (ADD_MONTHS (SYSDATE, -(LEVEL - 1))) LAST_DAY
      FROM   DUAL
CONNECT BY   LEVEL <= 24;

Answer 2

Here you go:

WITH cteTwo_years_ago AS (SELECT TRUNC(SYSDATE - INTERVAL '2' YEAR, 'MONTH') AS TWO_YEARS_AGO
                            FROM DUAL),
     cteMonth_intervals AS (SELECT INTERVAL '1' MONTH * (LEVEL-1) AS SEQ
                              FROM DUAL
                              CONNECT BY LEVEL-1 < 24),
     cteDates AS (SELECT TWO_YEARS_AGO + SEQ AS MONTH_START
                    FROM cteTwo_years_ago
                    CROSS JOIN cteMonth_intervals)
SELECT LAST_DAY(MONTH_START)
  FROM cteDates

Answer 3

First and last date of each month in interval from given date up today.

DEFINE start_date=TO_DATE('01.01.2020','DD.MM.YYYY');
SELECT
    ADD_MONTHS(&start_date,LEVEL-1) first_day_in_month,
    ADD_MONTHS(&start_date,LEVEL)-1 last_day_in_month
FROM DUAL CONNECT BY LEVEL <= FLOOR(MONTHS_BETWEEN(SYSDATE,&start_date));