jmake
30 de Noviembre del 2007

La siguiente funci贸n: horaslaborables, devuelve las horas que han transcurrido entre un rango de fechas dado, teniendo en cuenta solo un horario de 8 a 20 horas y de Lunes a Viernes.
Ejemplo:
select horaslaborables(TO_DATE('25-11-2007 15:00','DD-MM-YYYY HH24:MI' ),
TO_DATE('27-11-2007 15:30','DD-MM-YYYY HH24:MI' )) HORAS_TRANSCURRIDAS
from dual
HORAS_TRANSCURRIDAS
19,5



Definici贸n de la funci贸n:

CREATE OR REPLACE FUNCTION horaslaborables(start_date IN DATE, end_date IN DATE)
RETURN NUMBER
IS
retval NUMBER(15,7);
retvalfin NUMBER(15,7);
new_start_date date;
new_end_date date;
bdaystart number(15,15);
bdayend number(15,15);
BEGIN
new_start_date := start_date;
new_end_date := end_date;
-- set defaults for business day start and end. Can be overridden per region
bdaystart := 8/24;
bdayend := 20/24;
--Start After end of day, make start be start of next day
if new_start_date-trunc(new_start_date)>bdayend then
new_start_date := TRUNC(new_start_date+1)+bdaystart;
end if;
--Start before start of day, make start be start of same day
if new_start_date-trunc(new_start_date) < bdaystart then
new_start_date := TRUNC(new_start_date) + bdaystart;
end if;
--Start Saturday, make start be Monday start of day
if to_char(new_start_date,'D')=6 THEN
new_start_date := TRUNC(new_start_date+2)+bdaystart;
END IF;
--Start Sunday, make start be Monday start of day
if to_char(new_start_date,'D')=7 THEN
new_start_date := TRUNC(new_start_date+1)+bdaystart;
END IF;
-- end after end of day, make end be end of day same day
if new_end_date-trunc(new_end_date) > bdayend then
new_end_date := trunc(new_end_date) + bdayend;
end if;
-- end before start of day, make end be start of day the same day
if new_end_date-trunc(new_end_date) < bdaystart then
new_end_date := trunc(new_end_date) + bdaystart;
end if;
--end on Saturday, make it be the end of the day on Friday
if to_char(new_end_date,'D')=6 then
new_end_date := trunc(new_end_date-1) + bdayend;
end if;
--end on Sunday, make it be the end of the day on Friday
if to_char(new_end_date,'D')=7 then
new_end_date := trunc(new_end_date-2) + bdayend;
end if;
--factor out weekend days
retval := new_end_date - new_start_date -
((TRUNC(new_end_date,'D') - TRUNC(new_start_date,'D'))/7)*2;
--pasamos a horas el resultado final, teniendo en cuenta que un d铆a completo son 12 horas
retvalfin := (retval)*24 - (TRUNC (retval)*12);
-- if holidays were to be calculated, the calculation would go here
-- if end is during nonbusiness hours, difference could be negative
if retval < 0 then
retvalfin := 0;
end if;
RETURN(retvalfin);
END;

Pedro Alarcon
12 de Octubre del 2014

Estimado,

junto con saludarte, te consulto por una funcion similar para mysql.

Necesito calcular las horas habiles transcurridas entre dos fechas y horas, el formato dd-mm-yyyy hh:mm:ss

saludos y gracias por tu ayuda.

atte.