How to get all dates in a month in Oracle?

How to get all dates in a month in Oracle?

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.
  • SQL
  • 1 min read

Use connect by clause to get all the dates in a month in Oracle. Below are the examples:

Example-1:

The following SQL query uses the trunc() function to get the numeric month value with connect by clause to get all the dates in a month:

SELECT TRUNC(SYSDATE, 'MM') + LEVEL - 1 AS day
    FROM dual
    CONNECT BY 
    TRUNC(TRUNC(SYSDATE, 'MM') + LEVEL - 1, 'MM') = TRUNC(SYSDATE, 'MM');

Output:

DAY
11/01/2022
11/02/2022
11/03/2022
11/04/2022
11/05/2022
11/06/2022
11/07/2022
11/08/2022
11/09/2022
11/10/2022
11/11/2022
11/12/2022
11/13/2022
11/14/2022
11/15/2022
11/16/2022
11/17/2022
11/18/2022
11/19/2022
11/20/2022
11/21/2022
11/22/2022
11/23/2022
11/24/2022
11/25/2022
11/26/2022
11/27/2022
11/28/2022
11/29/2022
11/30/2022

Example-2

Below SQL query gives the same output but the logic is a little different:

select trunc(sysdate, 'MONTH') + rownum - 1
   from dual
   connect by rownum <= to_number(to_char(last_day(sysdate), 'DD'));

Related: