List Dates in a month

Your rating: None Average: 3.8 (5 votes)

Here is an SQL that will list all dates for a particular month.

SELECT       TRUNC (TO_DATE (:target_date, 'MM/DD/YYYY'),
                    'MM'
                   )
           + LEVEL
           - 1 AS days_in_month
      FROM DUAL
CONNECT BY LEVEL <=
              EXTRACT (DAY FROM LAST_DAY (TRUNC (TO_DATE (:target_date,
                                                          'MM/DD/YYYY'
                                                         ),
                                                 'MM'
                                                )
                                         )
                      );

DAYS_IN_MONTH
1/1/2008
1/2/2008
1/3/2008
1/4/2008
1/5/2008
1/6/2008
1/7/2008
1/8/2008
1/9/2008
1/10/2008
1/11/2008
1/12/2008
1/13/2008
1/14/2008
1/15/2008
1/16/2008
1/17/2008
1/18/2008
1/19/2008
1/20/2008
1/21/2008
1/22/2008
1/23/2008
1/24/2008
1/25/2008
1/26/2008
1/27/2008
1/28/2008
1/29/2008
1/30/2008
1/31/2008

Lets brake it down and try to understand it. To get all dates in a month, we need to get the first date, the number of days in the month and then a way to be able to loop through and create dates in between.
--Brings back the first date in the month
SELECT TRUNC (TO_DATE (:target_date, 'MM/DD/YYYY'), 'MM') AS fist_date
  FROM DUAL;

FIST_DATE
1/1/2008

--Brings back number of days in the month                      
SELECT EXTRACT (DAY FROM LAST_DAY (TRUNC (TO_DATE (:target_date, 'MM/DD/YYYY'),
                                          'MM'
                                         )
                                  )
               ) AS number_days
  FROM DUAL;

NUMBER_DAYS
31

Please try to help out with unanswered topics on the forum. Chances are you have had the same issue/question some time in your IT career!