إنتقال للمحتوى

  • تسجيل الدخول عبر الفيس بوك تسجيل الدخول عبر تويتر Log In with LinkedIn Log In with Google      تسجيل دخول    
  • إنشاء حساب

صورة
- - - - -

Calculate The Number Of Minutes Between Two Date-time Values And Skipping Weekends


لا توجد ردود على هذا الموضوع

#1 alsalami

alsalami

    عضو

  • الأعضــاء
  • 9 مشاركة

تاريخ المشاركة 29 May 2007 - 11:46 AM

I need to write function that calculate The Number Of Minutes Between Two Date-Time Values and Skipping Weekends
Also skip the time between 03:01:00 pm to 06:59:00 Am

I need to enter the two date with time the return value should be the number of Minutes between to days without weekend days and the time between 7:00 AM to 3:00 PM


create or replace function workingday (d1 date, d2 date) return number
this function must subtract the weekend day (friday and Saturday)
for example
'select workingday('1-may-07',20-may-07’)
the result must be 14
create or replace function workingday (d1 date , d2 date) return number
is
total number;
startdate date ;
DayNum POSITIVE;

begin
total := 0;
startdate := d1;


while (startdate <= d2) loop
startdate := startdate +1;
DayNum := TO_CHAR(startdate, 'D');

IF DayNum BETWEEN 2 AND 6 THEN
total :=total+1;
end if;

end loop;
return total;

end workingday;