Query Corner

Query to get all days in current year

Oracle 10g Version

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);

Oracle earlier versions

(Note that there must be at least 365 objects in the schema)

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);

Write comments for the query at my blog