Detailed Calender Query

SELECT TRUNC( sd + rn ) time_id,
TO_CHAR( sd + rn, 'fmDay' ) day_name,
TO_CHAR( sd + rn, 'D' ) day_number_in_week,
TO_CHAR( sd + rn, 'DD' ) day_number_in_month,
TO_CHAR( sd + rn, 'DDD' ) day_number_in_year,
TO_CHAR( sd + rn, 'W' ) calendar_week_number,
( CASE WHEN TO_CHAR( sd + rn, 'D' ) IN ( 1, 2, 3, 4, 5, 6 ) THEN
NEXT_DAY( sd + rn, 'SATURDAY' ) ELSE
( sd + rn ) END ) week_ending_date,
TO_CHAR( sd + rn, 'MM' ) calendar_month_number,
TO_CHAR( LAST_DAY( sd + rn ), 'DD' ) days_in_cal_month,
LAST_DAY( sd + rn ) end_of_cal_month,
TO_CHAR( sd + rn, 'FMMonth' ) calendar_month_name,
( ( CASE WHEN TO_CHAR( sd + rn, 'Q' ) = 1 THEN
TO_DATE( '03/31/' || TO_CHAR( sd + rn, 'YYYY' ), 'MM/DD/YYYY' )
WHEN TO_CHAR( sd + rn, 'Q' ) = 2 THEN
TO_DATE( '06/30/' || TO_CHAR( sd + rn, 'YYYY' ), 'MM/DD/YYYY' )
WHEN TO_CHAR( sd + rn, 'Q' ) = 3 THEN
TO_DATE( '09/30/' || TO_CHAR( sd + rn, 'YYYY' ), 'MM/DD/YYYY' )
WHEN TO_CHAR( sd + rn, 'Q' ) = 4 THEN
TO_DATE( '12/31/' || TO_CHAR( sd + rn, 'YYYY' ), 'MM/DD/YYYY' )
END ) - TRUNC( sd + rn, 'Q' ) + 1 ) days_in_cal_quarter,
TRUNC( sd + rn, 'Q' ) beg_of_cal_quarter,
( CASE WHEN TO_CHAR( sd + rn, 'Q' ) = 1 THEN
TO_DATE( '03/31/' || TO_CHAR( sd + rn, 'YYYY' ), 'MM/DD/YYYY' )
WHEN TO_CHAR( sd + rn, 'Q' ) = 2 THEN
TO_DATE( '06/30/' || TO_CHAR( sd + rn, 'YYYY' ), 'MM/DD/YYYY' )
WHEN TO_CHAR( sd + rn, 'Q' ) = 3 THEN
TO_DATE( '09/30/' || TO_CHAR( sd + rn, 'YYYY' ), 'MM/DD/YYYY' )
WHEN TO_CHAR( sd + rn, 'Q' ) = 4 THEN
TO_DATE( '12/31/' || TO_CHAR( sd + rn, 'YYYY' ), 'MM/DD/YYYY' )
END ) end_of_cal_quarter,
TO_CHAR( sd + rn, 'Q' ) calendar_quarter_number,
TO_CHAR( sd + rn, 'YYYY' ) calendar_year,
( TO_DATE( '12/31/' || TO_CHAR( sd + rn, 'YYYY' ), 'MM/DD/YYYY' )
- TRUNC( sd + rn, 'YEAR' ) ) days_in_cal_year,
TRUNC( sd + rn, 'YEAR' ) beg_of_cal_year,
TO_DATE( '12/31/' || TO_CHAR( sd + rn, 'YYYY' ), 'MM/DD/YYYY' ) end_of_cal_year
FROM ( SELECT TO_DATE( '12/31/2002', 'MM/DD/YYYY' ) sd,
rownum rn FROM dual CONNECT BY level <= 6575 )
/

Calender,Dates,Objects created

Calender of a year


select lpad( Month, 20-(20-length(month))/2 ) month
, "Su" , "Mo" , "Tu" , "We" , "Th" , "Fr" , "Sa"
from ( select to_char(dt,'fmMonthfm YYYY') month
, to_char(dt+1,'iw') week
, max(decode(to_char(dt,'d'),'1',lpad(to_char(dt,'fmdd'),2))) "Su"
, max(decode(to_char(dt,'d'),'2',lpad(to_char(dt,'fmdd'),2))) "Mo"
, max(decode(to_char(dt,'d'),'3',lpad(to_char(dt,'fmdd'),2))) "Tu"
, max(decode(to_char(dt,'d'),'4',lpad(to_char(dt,'fmdd'),2))) "We"
, max(decode(to_char(dt,'d'),'5',lpad(to_char(dt,'fmdd'),2))) "Th"
, max(decode(to_char(dt,'d'),'6',lpad(to_char(dt,'fmdd'),2))) "Fr"
, max(decode(to_char(dt,'d'),'7',lpad(to_char(dt,'fmdd'),2))) "Sa"
from ( select trunc(sysdate,'y')-1+rownum dt
from all_objects
where rownum <= add_months(trunc(sysdate,'y'),12)
-
trunc(sysdate,'y')
)
group by to_char(dt,'fmMonthfm YYYY')
, to_char( dt+1, 'iw' )
)
order by to_date( month, 'Month YYYY' )
, to_number(week)

--------------------------------------------------
To get all Dates in a Year

select mydate,to_char(mydate,'Day') from(
select (level-1)+to_date('01-01-'||to_char(sysdate,'yyyy'),'dd-mm-yyyy') mydate from dual
connect by level <=to_date('31-12-'||to_char(sysdate,'yyyy'),'dd-mm-yyyy')-to_date('01-01-'||to_char(sysdate,'yyyy'),'dd-mm-yyyy') +1)

OR


select mydate,to_char(mydate,'Day') from(
select (rownum-1)+to_date('01-01-'||to_char(sysdate,'yyyy'),'dd-mm-yyyy') mydate from all_objects
where
rownum <=to_date('31-12-'||to_char(sysdate,'yyyy'),'dd-mm-yyyy') - to_date('01-01-'||to_char(sysdate,'yyyy'),'dd-mm-yyyy') +1)

The above query assumes that all_objects returns at least 365 records.

----------------------------------------------------------------------------
Number of objects created in a month of year

select to_char(trunc(created,'MONTH'),'YYYY-MON') "Month", count(*) "Nb"
from user_objects group by trunc(created,'MONTH')
order by trunc(created,'MONTH');