Re: Oracle calendar teaser...

Hi folks, I tried this and it seems to work for all the tests I did.

set verify off
col sd form a5 head 'Start|Day'
col ed form a5 head 'End|Day'
accept start_date number prompt 'Start Date: ' accept end_date number prompt 'End Date: '

select floor((to_date(&end_date,'YYYYMMDD')

              -to_date(&start_date,'YYYYMMDD')+1)/7) weeks,
       mod((to_date(&end_date,'YYYYMMDD')-to_date(&start_date,'YYYYMMDD')+1),7)
days,
       to_char(to_date('&start_date','YYYYMMDD'),'D') sd,
       to_char(to_date('&end_date','YYYYMMDD'),'D') ed

col wd form 9999 head 'Week|Days'
accept s number prompt 'Start Day: '
accept e number prompt 'End Day: '
select decode(greatest(&s,&e),&e,&e-&s+1-decode(&s,1,1,0)-decode(&e,7,1,0),

              &e+7-&s-1) wd
from dual
/

This could have been written as one SQL if I replaced the &s and &e above with the sd and se columns from the first SQL. I didn't do it to keep it clearer (?!?). Looks strange but it works.

Martin Received on Wed Nov 18 1998 - 20:49:30 CET