Kancha:
I did not understand your query. Can you elaborate it please ?

Reddy: Sorry I should have explained what I want to do before.

I want to calculate number of business days so I don't want to include Saturdays and Sundays.
Also for calculating number of business days I want to use Monday as start date(if the start date entered is sat or sun)
and use Friday as the end date(if the end date entered is sat or sun), for any other day use that date as start and end date.

I have also written function to do this:


Create or replace function num_Business_Days(start_date in date, end_date in date)
return number is
currdate date := start_date; /* holds the next date */
theDay varchar2(10);/* day of the week for currdate */
countBusiness number := 0; /* counter for business days */

begin

loop

/* finished if end_date is reached */
exit when currdate = end_date+1;

/* what day of the week is it? */
select to_char(currdate,'fmDay') into theDay from dual;

/* count it only if it is a weekday */
if theDay <> 'Saturday' and theDay <> 'Sunday' then
countBusiness := countBusiness + 1;
end if;

/* go to the next day */
currdate := to_date(currdate+1);

end loop;
return (countBusiness);
end;
/

But I am not sure if this is efficient way of doing it because it loops through each day between start and end date.

thanks
Sonali