Galera, someone there, have a ready function that brings me the number of days between two dates?
Number of days between two dates
- dr_gori
- 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
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.
Date2 - Date1 will return the number of days between the two dates.
- dr_gori
- 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
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.
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.
create or replace function dias_data( d1 date, d2 date) return number
is
begin
return d2 - d1;
end;
/
- Toad
- 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
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
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
- dr_gori
- 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
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.
Let's go to example:
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.
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;
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.
- Toad
- 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
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:
But it is giving error in "from (SELECT" ...
I tried to do via cursor too. It was like this:
]
But it also gives error in parentheses ..... What am I doing wrong?:?: [99]
Hugs and thank you from now!
Matheus
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:
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:
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
- dr_gori
- 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
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:
testing:
: -O
must be date ...
does:
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;
SQL> select dias_uteis(sysdate-30, sysdate) uteis from dual;
UTEIS
----------
21
SQL>
: -O
- Toad
- 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
matheus.dev
twitter.com/developer__c
I have now included the option for him not to count national holidays.
was like this:
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
was like this:
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;
Thanks and hugs!
Matheus
- dr_gori
- 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
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!
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!
- Toad
- 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
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.
Thanks.
- Toad
- 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
matheus.dev
twitter.com/developer__c
Following this line of reasoning, the function was thused:
and the table thus:
Now, if you need to change the dates, just handle the table and not in the function!
Hugs!
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:
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.
Hugs!
- leobbg
- 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 !!
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 !!
-
- 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:
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:
Performing an example:
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:
Two holiday days is because of the carnival registered for days 16 and 17/02/2015.
Result does not tell Sunday:
Result does not count Saturday and Sunday:
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:
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:
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;
RETURN (total_dias_uteis-total_feriados);
SELECT DIAS_UTEIS(2,TO_DATE('2015-02-01'),TO_DATE('2015-02-28'),0) FROM DUAL;
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:
TOTAL DIAS:28 - TOTAL FERIADOS:2 - DIAS UTEIS:28 - DIAS UTEIS TOTAL:26
SELECT DIAS_UTEIS(2,TO_DATE('2015-02-01'),TO_DATE('2015-02-28'),1) FROM DUAL;
TOTAL DIAS:28 - TOTAL FERIADOS:2 - DIAS UTEIS:24 - DIAS UTEIS TOTAL:22
SELECT DIAS_UTEIS(2,TO_DATE('2015-02-01'),TO_DATE('2015-02-28'),2) FROM DUAL;
TOTAL DIAS:28 - TOTAL FERIADOS:2 - DIAS UTEIS:20 - DIAS UTEIS TOTAL:18
Doubts, just ask.
Thanks to the others who helped indirectly.
Holiday table:
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';
-
- Information
-
Who is online
Users browsing this forum: No registered users and 10 guests