CODEWITHSUNDEEP
sqloracle-databasedate
Pugzly
Pugzly

Reputation: 934

First_day last_day starting from January

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

Upvotes: 0

Views: 47

Answers (1)

Littlefoot
Littlefoot

Reputation: 142968

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>

Upvotes: 1

Related Questions