Number of days between two dates

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
  

Poston Wed, 09 Nov 2005 2:40 pm

Guys, someone there has a function ready q me bring the number of days between two dates?
sydfilho
Location: porto alegre

Poston Wed, 09 Nov 2005 2:50 pm

Yes, it's the minus sign.
Date1-date2 will return the number of days between the two dates.
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Wed, 09 Nov 2005 2:53 pm

td, I was just wondering if anyone else has had a funçãozinha q passing date as parameter, returns the value, to get a head start here.qualquer way, thanks
sydfilho
Location: porto alegre

Poston Wed, 09 Nov 2005 3:00 pm

But I don't understand. what you want a function to do this?
If you have 2 dates and want to know the number of days between them, just subtratir each other.

You can make a function for that, but just to confuse who will move into the system later.

Code: Select all
create or replace function dias_data( d1 date, d2 date) return number
is
begin
  return  d2 - d1;
end;
/
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Thu, 02 Feb 2006 8:22 am

I don't know if that's the doubt him, but I'vê just come across this problem.

I have a date Date1 and Date2 date.

How do I calculate how many working days between Date1 and Date2. (Does not count weekends or holidays).

I imagine that take a huge function ... does anyone have something lying around?

Hugs!!
Thank you!

Toad
Toad
Location: Seattle, WA

Matheus H. Gonçalves
www.toad.com.br
www.twitter.com/toadgeek

Poston Fri, 03 Feb 2006 8:55 am

I think the easiest way is to create a program which tells you how many days are between a date and another and loops. If the weekday is Monday through Friday, sum on a counter.

I made a SQL here that does more or less the same thing. Maybe do a routine in PL/SQL is faster than this sql.

Code: Select all
declare
  vtemp  number;
  vdata1 date;
  vdata2 date;
begin
  vdata1 := to_date('03/02/2006','dd/mm/rrrr');
  vdata2 := to_date('28/02/2006','dd/mm/rrrr');

  select count(dat)
  into vtemp
  from
    (
    select vdata1 + rownum-1 dat
    from all_tables
    where rownum <= ( vdata2-vdata1)+1
    )
  where to_char(dat, 'd') not in (1,7);
 
  dbms_output.put_line('Numero de dias úteis: '||vtemp);
end;
for example:
Code: Select all
SQL> set serveroutput on
SQL> declare
  2    vtemp  number;
  3    vdata1 date;
  4    vdata2 date;
  5  begin
  6    vdata1 := to_date('03/02/2006','dd/mm/rrrr');
  7    vdata2 := to_date('28/02/2006','dd/mm/rrrr');
  8 
  9    select count(dat)
10    into vtemp
11    from
12      (
13      select vdata1 + rownum-1 dat
14      from all_tables
15      where rownum <= ( vdata2-vdata1)+1
16      )
17    where to_char(dat, 'd') not in (1,7);
18   
19    dbms_output.put_line('Numero de dias úteis: '||vtemp);
20  end;
21  /
Numero de dias úteis: 18

PL/SQL procedure successfully completed.

SQL>
Last edited by dr_gori on Mon, 06 Feb 2006 8:39 am, edited 1 time in total.
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Mon, 06 Feb 2006 8:34 am

Speech Dr. all quiet? Thanks in advance for your help.

I tried to generate a function with the sql that you spent, it was more or less like this:
Code: Select all
FUNCTION DIAS_UTEIS(DATA1 IN DATA, DATA2 IN DATA) RETURN NUMBER IS

  vdata1 date := to_date(DATA1,'dd/mm/rrrr');
  vdata2 date := to_date(DATA2,'dd/mm/rrrr');
  vtemp  number;

begin

  select count(dat)into vtemp
  from (select vdata1 + rownum-1 dat from all_tables where rownum <= ( vdata2-vdata1)+1 )
  where to_char(dat, 'd') not in (1,7);
  RETURN vtemp;

end;
but it is giving error in \"from (select\" ...

I tried to do via cursor also. Was like this:
Code: Select all
FUNCTION DIAS_UTEIS(DATA1 IN DATA, DATA2 IN DATA) RETURN NUMBER IS



  vdata1 date := to_date(DATA1,'dd/mm/rrrr');
  vdata2 date := to_date(DATA2,'dd/mm/rrrr');
  vtemp  number;

  cursor DATA is   select count(dat)
                   from (
                          select vdata1 + rownum-1 dat
                          from all_tables where rownum <= ( vdata2-vdata1)+1
                         )
                   where to_char(dat, 'd') not in (1,7);
begin
  OPEN  DATA;
  FETCH DATA INTO vtemp;
  CLOSE DATA;
  RETURN vtemp;
end;
but also gives error in parentheses ... What am I doing wrong?:?: hugs and thanks in advance!

Matheus
Toad
Location: Seattle, WA

Matheus H. Gonçalves
www.toad.com.br
www.twitter.com/toadgeek

Poston Mon, 06 Feb 2006 8:52 am

The problem is that you are declaring Date1 IN date ...
Must be DATE ...

Do this:
Code: Select all
create or replace FUNCTION DIAS_UTEIS( vDATA1 IN DATE, vDATA2 IN DATE) RETURN NUMBER IS
  vtemp  number;
begin
  select count(dat)into vtemp
  from (select vdata1 + rownum-1 dat from all_tables where rownum <= ( vdata2-vdata1)+1 )
  where to_char(dat, 'd') not in (1,7);
  RETURN vtemp;
end;
Testing:
Code: Select all
SQL> select  dias_uteis(sysdate-30, sysdate) uteis from dual;

     UTEIS
----------
        21

SQL>
:-
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Mon, 06 Feb 2006 9:17 am

Nuossa!! Pretend you didn't see that, beleza!? hahahahaha Brigadao!!

Hugs
Toad
Location: Seattle, WA

Matheus H. Gonçalves
www.toad.com.br
www.twitter.com/toadgeek

Poston Mon, 06 Feb 2006 11:48 am

Include the option now for him not to tell the national holidays.

Was like this:
Code: Select all
create or replace FUNCTION DIAS_UTEIS( vDATA1 IN DATE, vDATA2 IN DATE) RETURN NUMBER IS
  vtemp  number;
  fer_01 varchar2(5) := '01/01';
  fer_02 varchar2(5) := '14/04';
  fer_03 varchar2(5) := '21/04';
  fer_04 varchar2(5) := '01/05';
  fer_05 varchar2(5) := '15/06';
  fer_06 varchar2(5) := '07/09';
  fer_07 varchar2(5) := '12/10';
  fer_08 varchar2(5) := '02/11';
  fer_09 varchar2(5) := '15/11';
  fer_10 varchar2(5) := '25/12';
begin
  select count(dat)into vtemp
  from (select vdata1 + rownum-1 dat from i_os where rownum <= (vdata2-vdata1)+1 )
  where to_char(dat, 'd') not in (1,7)
        and (to_char(dat,'dd/mm') <> fer_01)
        and (to_char(dat,'dd/mm') <> fer_02)
        and (to_char(dat,'dd/mm') <> fer_03)
        and (to_char(dat,'dd/mm') <> fer_04)
        and (to_char(dat,'dd/mm') <> fer_05)
        and (to_char(dat,'dd/mm') <> fer_06)
        and (to_char(dat,'dd/mm') <> fer_07)
        and (to_char(dat,'dd/mm') <> fer_08)
        and (to_char(dat,'dd/mm') <> fer_09)
        and (to_char(dat,'dd/mm') <> fer_10);
  vtemp := vtemp - 1;
  RETURN vtemp;
end;
perhaps using and (to_char (dat, ' dd/mm ') not in (fer_01, fer_02 ... also works, but not tested.

Thanks and hugs!

Matheus
Toad
Location: Seattle, WA

Matheus H. Gonçalves
www.toad.com.br
www.twitter.com/toadgeek

Poston Mon, 06 Feb 2006 11:51 am

I think that ideally, you create a table of holidays.
Then all you have to do is put NOT IN (select holidays from TB_FERIADOS) ... you know?

That way, you are plastering the holidays. Each time, you're going to have to change the routine. Also you can't treat holidays specific to each State of form in a cast ...

With a table, everything is well flexible!
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Mon, 06 Feb 2006 12:12 pm

Of course ... you're right ... the idea is actually only with national holidays, but with a table, and not in a cast, there is some amendment and/or addition of holiday, is much simpler.
Thank you.
Toad
Location: Seattle, WA

Matheus H. Gonçalves
www.toad.com.br
www.twitter.com/toadgeek

Poston Mon, 06 Feb 2006 3:05 pm

Following this line of reasoning was thus:
Code: Select all
create or replace FUNCTION DIAS_UTEIS( vDATA1 IN DATE, vDATA2 IN DATE) RETURN NUMBER IS
  vtemp  number;
begin
  select count(dat)into vtemp
  from (select vdata1 + rownum-1 dat from i_os where rownum <= (vdata2-vdata1)+1 )
  where to_char(dat, 'd') not in (1,7)
        and to_char(dat,'dd/mm') not in (select dia from i_feriado);
  vtemp := vtemp - 1;
  RETURN vtemp;
end;
and the table like this:
Code: Select all
SQL> desc i_feriado
Name                            Null?    Type
------------------------------- -------- ----
CODIGO                                   NUMBER(5)
DIA                                      VARCHAR2(5)
DESCRICAO                                VARCHAR2(60)

SQL> select * from i_feriado
  2  /

   CODIGO DIA   DESCRICAO
--------- ----- ------------------------------------------------------------
        1 01/01 Confraternizacao Universal
        2 14/04 Paixão de Cristo
        3 21/04 Tiradentes
        4 01/05 Dia do Trabalho
        5 15/06 Corpus Christi
        6 09/07 Data Magna de SP
        7 20/08 Aniversário SBC
        8 07/09 Independencia
        9 12/10 Nossa Senhora Aparecida
       10 02/11 Finados
       11 15/11 Proclamação da República
       12 25/12 Natal

12 rows selected.
Now, if you need to change the dates, it's just moving on the table and not in function.

Hugs!
Toad
Location: Seattle, WA

Matheus H. Gonçalves
www.toad.com.br
www.twitter.com/toadgeek

Poston Tue, 07 Feb 2006 3:29 pm

Man, only to supplement, ...

It is better to include the year in the tmb holidays.. because there are some holidays that change from one year to another an example of which is the Carnival.

Thanks a lot!! 8)
leobbg
Location: PORTO ALEGRE - RS

Leo BBG Consultor Oracle

Poston Thu, 09 Feb 2006 11:54 am

Is not registered there, but in my table also has the holidays of Carnival since 1990. Interest to someone, just ask!
Hugs!
Toad
Location: Seattle, WA

Matheus H. Gonçalves
www.toad.com.br
www.twitter.com/toadgeek

Poston Thu, 17 Dec 2015 7:09 pm

Hello guys. I was looking for something like this on the net because it faced this dilemma to calculate working days at Oracle.

Tired of searching, I decided to create a function for this. She tells everyone apart from registered local and national holidays in the table and in case you want to account for example consider Saturday and/or Sunday as a working day. The latter, because I work with training classes and some of them were invoiced by the supplier also on Saturdays and/or Sundays, so I had to consider how business day.

The holiday table this separated by State because as has local holidays, had to separate as well.

The function was thus:
Code: Select all
create or replace FUNCTION DIAS_UTEIS(vCOD_UF INT, vDATAI IN DATE, vDATAF IN DATE, PERIODO IN INT) RETURN VARCHAR2 AS
total_dias NUMBER;
total_feriados NUMBER;
total_dias_uteis NUMBER := 0;
/*
s = dias da semana que devemos considerar
0 conta sabado e domingo
1 não conta domingo
2 não conta sabado e domingo
*/
begin

  /*
  CONTA A QUANTIDADE DE DIAS ENTRE AS DATAS
  SEM VERIFICAR FERIADOS, SÁBADOS E DOMINGOS
  */
  FOR CUR_ROW IN (SELECT TO_DATE(vDATAF)-TO_DATE(vDATAI) TTDIAS FROM DUAL)
    LOOP
      total_dias:=CUR_ROW.TTDIAS;
    END LOOP;
 
  /*
  CHECA SE EXISTE ALGUM DIA ENTRE AS DATAS NA LISTA DE FERIADOS E SEPARA A CONTAGEM PARA CONSIDERAR:
  0 = CONTA SÁBADO E DOMINDO
  1 = NÃO CONTA FERIADOS DE DOMINGO
  2 = NÃO CONTA FERIADOS DE SÁBADO E DOMINGO
 
  PORQUE ISSO?
  VOCÊ PODE QUERER CONSIDERAR SÁBADO COMO DIA UTIL POR EXEMPLO.
  */
  IF PERIODO = 0  THEN
    FOR CUR_ROW IN (SELECT COUNT(DATA_FERIADO) TT_FERIADOS FROM GIP_TAB_GBL_FERIADOS WHERE DATA_FERIADO BETWEEN TO_DATE(vDATAI) AND TO_DATE(vDATAF) AND COD_UF=vCOD_UF)
      LOOP
        total_feriados:=CUR_ROW.TT_FERIADOS;
      END LOOP;
  ELSIF PERIODO = 1 THEN
    FOR CUR_ROW IN (SELECT SUM(CASE WHEN TO_CHAR(DATA_FERIADO,'DY') NOT IN('DOM') THEN 1 ELSE 0 END) TT_FERIADOS FROM GIP_TAB_GBL_FERIADOS WHERE DATA_FERIADO BETWEEN TO_DATE(vDATAI) AND TO_DATE(vDATAF) AND COD_UF=vCOD_UF)
      LOOP
        total_feriados:=CUR_ROW.TT_FERIADOS;
      END LOOP;
  ELSIF PERIODO = 2 THEN
    FOR CUR_ROW IN (SELECT SUM(CASE WHEN TO_CHAR(DATA_FERIADO,'DY') NOT IN('SÁB','DOM') THEN 1 ELSE 0 END) TT_FERIADOS FROM GIP_TAB_GBL_FERIADOS WHERE DATA_FERIADO BETWEEN TO_DATE(vDATAI) AND TO_DATE(vDATAF) AND COD_UF=vCOD_UF)
      LOOP
        total_feriados:=CUR_ROW.TT_FERIADOS;
      END LOOP;
  END IF;
 
 
  /*
  PERCORRE DIA POR DIA SEMPRE CHECANDO SE DESEJA CONTAR OU NÃO SABADO E DOMINGO
  */
  FOR i IN 0 .. total_dias
    LOOP
      IF PERIODO = 0  THEN
        FOR CUR_ROW IN (SELECT TO_DATE(vDATAI)+i FROM DUAL)
          LOOP 
            total_dias_uteis:=total_dias_uteis+1;
          END LOOP;
      ELSIF PERIODO = 1 THEN
        FOR CUR_ROW IN (SELECT (CASE WHEN TO_CHAR(TO_DATE(vDATAI)+i,'DY') NOT IN('DOM') THEN 1 ELSE 0 END) TT FROM DUAL)
          LOOP 
            total_dias_uteis:=total_dias_uteis++CUR_ROW.TT;
          END LOOP;
      ELSIF PERIODO = 2 THEN
         FOR CUR_ROW IN (SELECT (CASE WHEN TO_CHAR(TO_DATE(vDATAI)+i,'DY') NOT IN('SÁB','DOM') THEN 1 ELSE 0 END) TT FROM DUAL)
          LOOP 
            total_dias_uteis:=total_dias_uteis+CUR_ROW.TT;
          END LOOP;
      END IF;
    END LOOP;

    total_dias:=total_dias+1;
   
   
  RETURN 'TOTAL DIAS:'||total_dias||' - TOTAL FERIADOS:'||total_feriados||' - DIAS UTEIS:'||total_dias_uteis||' - DIAS UTEIS TOTAL:'||(total_dias_uteis-total_feriados);
END;
the return of her this as varchar just to test the account and verify that you're doing okay. To get the final result, replace with:
Code: Select all
RETURN (total_dias_uteis-total_feriados);
Running an example:
Code: Select all
SELECT DIAS_UTEIS(2,TO_DATE('2015-02-01'),TO_DATE('2015-02-28'),0) FROM DUAL;
the first variable of the function is the state code, in my case I have 2 as GO;
The second is the starting time;
The third is the end date;
The fourth is the call to count or not Saturday and/or Sunday as below: 0 care Saturday and Sunday 1 doesn't count Sunday 2 doesn't count Saturday and Sunday This SELECT results in:
Code: Select all
TOTAL DIAS:28 - TOTAL FERIADOS:2 - DIAS UTEIS:28 - DIAS UTEIS TOTAL:26
* the two-day holiday is because registered for the Carnival days 16 and 2/17/2015.

Code: Select all
SELECT DIAS_UTEIS(2,TO_DATE('2015-02-01'),TO_DATE('2015-02-28'),1) FROM DUAL;
doesn't count Result Sunday:
Code: Select all
TOTAL DIAS:28 - TOTAL FERIADOS:2 - DIAS UTEIS:24 - DIAS UTEIS TOTAL:22
Code: Select all
SELECT DIAS_UTEIS(2,TO_DATE('2015-02-01'),TO_DATE('2015-02-28'),2) FROM DUAL;
Result does not count Saturday and Sunday:
Code: Select all
TOTAL DIAS:28 - TOTAL FERIADOS:2 - DIAS UTEIS:20 - DIAS UTEIS TOTAL:18
hope that helps many people as well as helped me and if anyone wants to improve or suggest something, feel free.

Any questions, just ask.

Thanks to the others who helped indirectly.


The holiday table:
Code: Select all
  CREATE TABLE "USER_GIP_HLG"."GIP_TAB_GBL_FERIADOS"
   (   "COD_UF" NUMBER(*,0),
   "DIA" NUMBER(*,0),
   "DATA_FERIADO" DATE,
   "FERIADO" VARCHAR2(200 BYTE),
   "NACIONAL" NUMBER(*,0)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TBL_BASE" ;

   COMMENT ON COLUMN "USER_GIP_HLG"."GIP_TAB_GBL_FERIADOS"."NACIONAL" IS '0 - NACIONAL | 1 - MUNICIPAL';
fabianomaca


  • See also
    Replies
    Views
    Last Post