First_day last_day starting from January-CodePudding

I have the following query, which is working as expected. My question how can I get the output starting from January for a specific year ie 2023 without hard coding a value.


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

FIRST_DAY    LAST_DAY
01-NOV-22.    30-NOV-22
01-DEC-22     31-DEC-22
01-JAN-23     31-JAN-23
01-FEB-23     28-FEB-23
01-MAR-23     31-MAR-23
01-APR-23     30-APR-23
01-MAY-23     31-MAY-23
01-JUN-23     30-JUN-23
01-JUL-23     31-JUL-23
01-AUG-23     31-AUG-23
01-SEP-23     30-SEP-23
01-OCT-23     31-OCT-23

CodePudding user response：

If you want a specific year (2023), how is your query supposed to know it unless you "hardocde" it? It doesn't have to be "2023" literally but e.g. "next year, according to SYSDATE") such as in this example:

SQL> with starting_date (datum) as
  2    (select add_months(trunc(sysdate, 'yyyy'), 12) from dual)
  3  select          add_months(datum, level - 1)  first_day,
  4         last_day(add_months(datum, level - 1)) last_day
  5  from starting_date
  6  connect by level <= 12;

FIRST_DAY LAST_DAY
--------- ---------
01-JAN-23 31-JAN-23
01-FEB-23 28-FEB-23
01-MAR-23 31-MAR-23
01-APR-23 30-APR-23
01-MAY-23 31-MAY-23
01-JUN-23 30-JUN-23
01-JUL-23 31-JUL-23
01-AUG-23 31-AUG-23
01-SEP-23 30-SEP-23
01-OCT-23 31-OCT-23
01-NOV-23 30-NOV-23
01-DEC-23 31-DEC-23

12 rows selected.

SQL>