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
Post Reply
sydfilho
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 7
Joined: Mon, 14 Mar 2005 3:36 pm
Location: porto alegre

Galera, someone there, have a ready function that brings me the number of days between two dates?
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

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

Yes, it's the least sign.
Date2 - Date1 will return the number of days between the two dates.
sydfilho
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 7
Joined: Mon, 14 Mar 2005 3:36 pm
Location: porto alegre

td well, I just wanted to know if anyone already had a mitigation that passing the date as a parameter, return the value, to advance me here .. qualquer way, thanks
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

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

But I do not understand .. why do you want a function for that?
If you have 2 dates and you want to know the number of days between them, just subtract one another.

You can do a function for this, but it's just to confuse who will mess with the system later.

Select all

create or replace function dias_data( d1 date, d2 date) return number 
is 
begin 
  return  d2 - d1; 
end; 
/ 
User avatar
Toad
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 253
Joined: Fri, 18 Nov 2005 2:14 pm
Location: Seattle, WA
Contact:
Matheus Gonçalves
matheus.dev
twitter.com/developer__c

I do not know if it's his doubt, but I just came across this problem.

I have a date date1 and a date date2.

How do I calculate how many business days there are between Data1 and Data2!? (Does not count weekends or national holidays).

I imagine that it will give a huge function ... does anyone have something related out there?

Hugs !!
Thanks!

Toad
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

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

I think the easiest way is to create a program that counts how many days has between one date and another and makes a loop. If the day of the week is Monday to Friday, sum in an accountant ...

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

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; 
Let's go to example:

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.
User avatar
Toad
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 253
Joined: Fri, 18 Nov 2005 2:14 pm
Location: Seattle, WA
Contact:
Matheus Gonçalves
matheus.dev
twitter.com/developer__c

Speak Dr.
Everything quiet? Thank you in advance for your help.

I tried to generate a function with the SQL that you passed, it was more or less like this:

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 too. It was like this:

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 it also gives error in parentheses ..... What am I doing wrong?:?: [99]
Hugs and thank you from now!

Matheus
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

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

The problem is that you are declaring Data1 in date ...
must be date ...

does:

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:

Select all

SQL> select  dias_uteis(sysdate-30, sysdate) uteis from dual; 
 
     UTEIS 
---------- 
        21 
 
SQL>  

: -O
User avatar
Toad
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 253
Joined: Fri, 18 Nov 2005 2:14 pm
Location: Seattle, WA
Contact:
Matheus Gonçalves
matheus.dev
twitter.com/developer__c

Nuossa !! Do you not see this, beleza!? hahahahaha

Brigadão !!

Hugs
User avatar
Toad
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 253
Joined: Fri, 18 Nov 2005 2:14 pm
Location: Seattle, WA
Contact:
Matheus Gonçalves
matheus.dev
twitter.com/developer__c

I have now included the option for him not to count national holidays.

was like this:

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; 
maybe using and (to_char (dat, 'dd / mm ') Not in (FER_01, FER_02 ... also work, but I have not tested!

Thanks and hugs!

Matheus
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

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

I think the ideal is you create a holiday table.
So you just put NOT IN (SELECT HOLIDAY from TB_FERIOS) ... SACA?

That way, you're pleading the holidays. Each time, you will have to change the routine ... Also you can not treat specific holidays from each state plastered ...

with a table, everything is fine flexible!
User avatar
Toad
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 253
Joined: Fri, 18 Nov 2005 2:14 pm
Location: Seattle, WA
Contact:
Matheus Gonçalves
matheus.dev
twitter.com/developer__c

Of course ... You are right ... In fact the idea is only with national holidays, but with a table, and not plastered, home exists some change and / or holiday addition, it is much simpler ...
Thanks.
User avatar
Toad
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 253
Joined: Fri, 18 Nov 2005 2:14 pm
Location: Seattle, WA
Contact:
Matheus Gonçalves
matheus.dev
twitter.com/developer__c

Following this line of reasoning, the function was thused:

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 thus:

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, just handle the table and not in the function!

Hugs!
User avatar
leobbg
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 22
Joined: Fri, 29 Oct 2004 10:25 am
Location: PORTO ALEGRE - RS
Leo BBG Consultor Oracle

Dude, just to complement, ...

It's best to include the year on the TMB holiday table. For there are some holidays that change from one year to another an example of them is the carnival.

Thanks !! 8)
User avatar
Toad
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 253
Joined: Fri, 18 Nov 2005 2:14 pm
Location: Seattle, WA
Contact:
Matheus Gonçalves
matheus.dev
twitter.com/developer__c

It's ... I did not register there, but on my table also has the carnaval holidays since 1990. If interested to someone, just ask!
Hugs!
fabianomaca
Rank: Estagiário Júnior
Rank: Estagiário Júnior
Posts: 1
Joined: Thu, 17 Dec 2015 6:43 pm

Hey guys. I was looking for something like that on the net because it faced this dilemma of calculating business days in Oracle.

Tired of looking, I decided to create a function for this. It counts taking out the local and national holidays registered in the table and account if you want for example to consider Saturday and / or Sunday as a business day. This last, because I work with training classes and some of them were billed by the supplier also on Saturdays and / or Sundays, so I had to consider as business day.

The holiday table is separated by state because as it has local holidays too, I had to separate it.

The function was like this:

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 is like Varchar only to test the account and verify that it is doing right. To pick up the end result, replace by:

Select all

RETURN (total_dias_uteis-total_feriados);
Performing an example:

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 the My case I have 2 as GO;
The second is the initial date;
The third is the final date;
The fourth is the indicative to count or not Saturday and / or Sunday as follows:
0 account Saturday and Sunday
1 Does not account Sunday
2 does not account Saturday and Sunday

This SELECT results in:

Select all

TOTAL DIAS:28 - TOTAL FERIADOS:2 - DIAS UTEIS:28 - DIAS UTEIS TOTAL:26
Two holiday days is because of the carnival registered for days 16 and 17/02/2015.

Select all

SELECT DIAS_UTEIS(2,TO_DATE('2015-02-01'),TO_DATE('2015-02-28'),1) FROM DUAL;
Result does not tell Sunday:

Select all

TOTAL DIAS:28 - TOTAL FERIADOS:2 - DIAS UTEIS:24 - DIAS UTEIS TOTAL:22

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:

Select all

TOTAL DIAS:28 - TOTAL FERIADOS:2 - DIAS UTEIS:20 - DIAS UTEIS TOTAL:18
I hope it helps a lot of people just as it helped me and if anyone wants to improve or suggest something, feel free.

Doubts, just ask.

Thanks to the others who helped indirectly.


Holiday table:

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';
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 10 guests