list dates in previous 4 weeks - oracle sql

i need to write some code to list all the days within the last 4 weeks of the report run date.

If the report was run today (29-sep-2011) i'd want the output to appear as in fig1.
The report would use the current week as week 4, and the 3 weeks prior to that.

If the report was run on 03-Oct-2011 id want the output to appeart as in fig2.

note that the weeks run from Monday - Sunday.

Further more i then want two time stamps to appear next to each day - the times are 10am and 4pm as displayed in fig3. I need the date/ timestamp to be in a usable format (ie not char) so that i can use the output later on to work out time durations.

thanks

sample.xls
tonMachine100Asked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
try this...


SELECT d + h / 24 asm_date, TO_CHAR(d + h / 24, 'Day') day_of_week
  FROM (SELECT NEXT_DAY(TRUNC(SYSDATE - 1), 'Sunday') - LEVEL + 1 d
          FROM DUAL
        CONNECT BY LEVEL <= 28),
       (SELECT 10 h FROM DUAL
        UNION ALL
        SELECT 16 FROM DUAL)
ORDER BY 1
0
 
tonMachine100Author Commented:
works great - thankyou
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.