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;
--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;
Bookmark/Search this post with:
Comments
Post new comment