Menu de navegação

[Eder PardeiroParticipante]

Bom dia pessoal,

Este será meu primeiro select em um banco Oracle, então nem é necessário dizer que sou totalmente leigo.

Preciso retornar em um conjunto de datas, somente as que estão na semana corrente. Por exemplo, se tratando desta semana que vai do dia 14/08/2016 até 20/08/2016, retornar datas que estão somente neste intervalo. Obviamente não quero informar as datas de inicio e fim, quero identificar a semana, encontrar o primeiro dia e o ultimo e retornar o intervalo.
Ainda seguindo a linha de raciocínio acima, tenho o exemplo:

Datas:
19/08/1980
14/05/1991
23/08/1987
10/08/1989
16/08/1975

Datas que estão na semana ATUAL:
19/08/1980
16/08/1975

Só pra entender melhor meu objetivo, quero retornar os aniversariantes da semana em uma tabela de funcionários.

[José Laurindo ChiappaModerador]

Blz ? Então, o conceito principal que vc tem que saber é que (vide Documentação Oracle) o RDBMS já tem o cálculo pra isso EMBUTIDO na função TO_CHAR (então é ILÓGICO vc querer fazer na mão), vide a máscara IW (ISO Week Number)…
Isso entendido, E sabendo que o dia de hoje no RDBMS Oracle vc obtém pela função SYSDATE, obter o número da semana é usar a máscara IW do TO_CHAR no SYSDATE, assim :

SQL> select sysdate, to_char(sysdate, ‘IW’) from dual;

SYSDATE TO

---

19/08/16 33

==> realmente, se formos conferir no calendário com número da semana (http://www.calendario-365.com.br/calend%C3%A1rio-2016.html) que realmente a semana 33 começou dia 15 e vai até dia 21….
Aí ficou simples, basta trazer as datas cujo número da semana seja o mesmo número da semana corrente, tipo :

SELECT colunasdesejadas
FROM nomedatabela
WHERE TO_CHAR(colunacomadatadoaniversario, ‘IW’) = TO_CHAR(SYSDATE, ‘IW’);

É ** simples ** assim, não faz sentido o que vc disse de calcular / encontrar dia da semana e início/fim, na mão… okdoc ??

Abraços,

José Laurindo Chiappa

OBS :

notar que a máscara IW usa o padrão internacional ISO, o mesmo usado nas agendas profissionais e calendários do comércio, onde a semana começa na segunda e termina no domingo, vide o link de exemplo que forneci : SE é isso que vc quer ok, nada a alterar…
Já se vc quer usar o calendário padrão religioso católico, onde a semana começa no Domingo pra terminar no Sábado, aí vc pula um dia, somando 1 na data : https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7482139067917 exemplifica….

[Paulo WerneckParticipante]

Eder, tenta algo desse tipo …

select * from TABELA
where CAMPO between trunc(sysdate, ‘D’) and (trunc(sysdate, ‘D’)+7);

[José Laurindo ChiappaModerador]

Paulo, afaik essa lógica não atende , pois ao que eu entendi o colega que perguntou quer consultar quem faz aniversário no mesmo número no ano da semana atual , em ANOS ANTERIORES evidentemente…

Eder, SE A LÓGICA DESEJADA é realmente pelo número da semana no calendário mesmo, é a lógica que indiquei… Temos que notar, porém, que os números dos dias no mês que formam uma dada semana sequencial no ano MUDAM cfrme o ano… Por exemplo, eu já tinha dado o link mostrando que em 2016 a nossa semana atual de número 33 vai de 15 a 21 de Agosto, porém http://www.calendario-365.com.br/calend%C3%A1rio-1989.html mostra que em 1989 essa semana 33 foi de 14 a 20 de agosto, http://www.calendario-365.com.br/calend%C3%A1rio-1980.html demonstra que em 1980 a semana de número 33 foi de 11 a 17 de Agosto (e assim PORTANTO a data que vc indicou de dia 10 ** não ** está na mesma semana), http://www.calendario-365.com.br/calend%C3%A1rio-1975.html mostra que em 1975 a semana com o mesmo número 33 da nossa atual no calendário foi de 11 a 17 de Agosto (portanto a data 16/08/1975 que vc indicou ENTRA sim…
Demonstração :

=> crio a tabela e criou uma massinha de testes :

SQL> CREATE TABLE TAB_ANIVERSARIOS (
2 NOME varchar2(30),
3 DATA_NASC date );

Tabela criada.

SQL> INSERT INTO TAB_ANIVERSARIOS values(‘JOAO’,to_date(’19/08/1980′, ‘DD/MM/YYYY’));

1 linha criada.

SQL> INSERT INTO TAB_ANIVERSARIOS values(‘JOSE’,to_date(’14/05/1991′, ‘DD/MM/YYYY’));

1 linha criada.

SQL> INSERT INTO TAB_ANIVERSARIOS values(‘LUIS’,to_date(’23/08/1987′, ‘DD/MM/YYYY’));

1 linha criada.

SQL> INSERT INTO TAB_ANIVERSARIOS values(‘ALEX’,to_date(’10/08/1989′, ‘DD/MM/YYYY’));

1 linha criada.

SQL> INSERT INTO TAB_ANIVERSARIOS values(‘GIGI’,to_date(’16/08/1975′, ‘DD/MM/YYYY’));

1 linha criada.

SQL> INSERT INTO TAB_ANIVERSARIOS values(‘LULU’,to_date(’21/08/1967′, ‘DD/MM/YYYY’));

1 linha criada.

SQL>

==> antes de fazer a pesquisa, pra fins didáticos vamos exibir o número da semana no ano dessas datas de nascimento E o número da semana de hoje :

SQL> select nome, data_nasc, to_char(DATA_NASC, ‘IW’) NUM_SEMANA from TAB_ANIVERSARIOS;

NOME DATA_NASC NU

---

JOAO 19/08/1980 34
JOSE 14/05/1991 20
LUIS 23/08/1987 34
ALEX 10/08/1989 32
GIGI 16/08/1975 33
LULU 21/08/1967 34

6 linhas selecionadas.

SQL> select sysdate, to_char(sysdate, ‘IW’) NUM_SEMANA from dual
2 ;

SYSDATE NU

---

19/08/2016 33

==> Então, pela lógica Rigorosa do Calendário, apenas a GIGI nasceu na mesma semana do ano que estamos hoje, que é a semana 33 :

SQL> select nome, data_nasc, to_char(DATA_NASC, ‘IW’) NUM_SEMANA_ANIV
from TAB_ANIVERSARIOS
where to_char(data_nasc, ‘IW’) = sysdate, ‘IW’) ;

NOME DATA_NASC NU

---

GIGI 16/08/1975 33

SQL>

==> ok ??? Se não é isso que vc quer – digamos, o que vc quer na verdade é achar o dia do mês em que começou a semana corrente e o dia que terminou, E aí listar todos os aniversários que caíram nesse intervalo – , isso é uma ** OUTRA LÓGICA, que NÂO TEM A VER com o NÚMERO DA SEMANA NO CALENDÁRIO cfrme os links expostos…
Isso SIM vc pode obter por TRUNC, talvez tenha sido ISSO que o Paulo quis indicar na resposta dele, tipo :

SQL> select trunc(sysdate,’w’) INICIO_SEMANA, trunc(sysdate,’w’)+6 ULTIMO_DIA_SEMANA from dual;

INICIO_SEM ULTIMO_DIA

---

15/08/2016 21/08/2016

SQL>

==> ok, bate com o calendário de 2016 que apontei na minha resposta anterior… Aí, se queremos listar os Aniversários que ocorreram do dia 15 ao dia 21 de agosto, seria Muito Simplesmente filtrar pelo dia e mês do começo da semana corrente até o dia e mês do fim da semana corrente – há trocentas maneiras de se fazer isso (inclusive via EXTRACT, que é uma funcionalidade ANSI) mas vou usar aqui conversões para string pegando só o dia e o mês via TO_CHAR :

SQL> select nome, data_nasc
2 from TAB_ANIVERSARIOS
3 where TO_DATE(to_char(data_nasc, ‘dd/mm’) || ‘/’ || to_char(sysdate, ‘YYYY’), ‘DD/MM/YYYY’)
4 BETWEEN trunc(sysdate,’w’) — dia de inicio da semana
5 AND trunc(sysdate,’w’)+6 — dia de fim da semana
6 ;

NOME DATA_NASC

---

JOAO 19/08/1980
GIGI 16/08/1975
LULU 21/08/1967

SQL>

==> aí sim ele trouxe pela lógica de dias embutidos entre o dia de começo da semana corrente E o dia de término dela, que foi de dia 15 a dia 21…. OK ??? UMA coisa é vc querer comparar pelo NÚMERO DA SEMANA NO CALENDÁRIO, e OUTRA COISA é comparar pelos DIA DE INÍCIO E FIM da semana corrente… Maçãs e bananas…

[]s

Chiappa

[Eder PardeiroParticipante]

Muito obrigado Chiappa!!! funcionou perfeitamente!!!

Utilizei o exemplo abaixo:

SQL> select nome, data_nasc
2 from TAB_ANIVERSARIOS
3 where TO_DATE(to_char(data_nasc, ‘dd/mm’) || ‘/’ || to_char(sysdate, ‘YYYY’), ‘DD/MM/YYYY’)
4 BETWEEN trunc(sysdate,’w’) — dia de inicio da semana
5 AND trunc(sysdate,’w’)+6 — dia de fim da semana
6 ;

[Eder PardeiroParticipante]

Só tenho mais uma dúvida agora….

O select está identificando que a semana começa na Segunda e termina no Domingo. Com exemplo em Agosto de 2016 (mês atual):

Só falta isso…

[José Laurindo ChiappaModerador]

OK, deduzo então que a lógica que vc quer não é obter o número da semana, mas sim obter o dia de início e fim da semana e fazer um BETWEEN nesse intervalo….
Muito bem : ** repetindo ** Novamente o que eu já tinha dito nas Respostas anteriores, a máscara IW (pra número da semana) ou W (para Dia na Semana) usam o padrão ocidental comercial (ISO), que é a semana começando na Segunda-feira, primeiro dia útil – SE vc quer encontrar na verdade o Domingo anterior, basta tirar um dia dessa data , o que no RDBMS Oracle se faz substraindo-se 1 da data… Observe :

==> realmente o algoritmo do W assume que a semana começou na Segunda-feira, que foi dia 29 :

SQL> select sysdate, trunc(sysdate,’w’) inicio_semana, trunc(sysdate,’w’)+6 fim_da_semana from dual;

SYSDATE INICIO_SEM FIM_DA_SEM

---

30/08/2016 29/08/2016 04/09/2016

==> para encontrar o Domingo imediatamente anterior á essa Segunda onde o algoritmo ISO acha que a semana começou, só faço -1… E como eu tirei um dia do início da semana , tiro um também do dia de final dela :

SQL> select sysdate, trunc(sysdate,’w’)-1 inicio_semana, trunc(sysdate,’w’)+5 fim_da_semana from dual;

SYSDATE INICIO_SEM FIM_DA_SEM

---

30/08/2016 28/08/2016 03/09/2016

==> Compreendido ? O exemplo fica então :

SQL> select nome, data_nasc
from TAB_ANIVERSARIOS
where TO_DATE(to_char(data_nasc, ‘dd/mm’) || ‘/’ || to_char(sysdate, ‘YYYY’), ‘DD/MM/YYYY’)
BETWEEN trunc(sysdate,’w’)-1 — dia de inicio da semana
AND trunc(sysdate,’w’)+5 — dia de fim da semana
;

Simples assim….

[]s

Chiappa

[Eder PardeiroParticipante]

“Simples assim”.. haha

Exatamente isso.. ficou perfeito… valeu!

[José Laurindo ChiappaModerador]

Jóia, fico contente de poder ter ajudado… Para vc se aprofundar em manipulação de datas no RDBMS Oracle (acho que o que ‘pegou’ mais pra vc foi isso, os conceitos de data no RDBMS Oracle, que – principalmente para a questão de Aritmética de datas e conversões – são Diferentes de outros SGBDs), além de uma estudada no manual de Concepts indico uma googlada para encontrar artigos sobre esse ponto , http://www.devmedia.com.br/como-trabalhar-com-data-e-hora-no-oracle/6985 ou http://verolme.blogspot.com.br/2009/11/aritmetica-de-datas-no-oracle.html são exemplos…

[]s

Chiappa

[Eder PardeiroParticipante]

Bom dia!

Vou olhar o material sim. Muito obrigado.

De qualquer forma vou incomodar mais uma vez… tive a seguinte surpresa hoje quando abri o portal: Ele identificou que a semana começa no dia 31 e termina no dia 6. Veja:

Agora veja como estou fazendo: 9(OBS: isso é ASP)


<%
' ## RETORNA DADOS.
Set cmdListaPessoas = Server.CreateObject("ADODB.Command")
cmdListaPessoas.ActiveConnection = ConectaDBOra
cmdListaPessoas.CommandText = "SELECT * FROM R034FUN where TO_DATE(to_char(DATNas, 'dd/mm') || '/' || to_char(sysdate, 'YYYY'), 'DD/MM/YYYY') BETWEEN trunc(sysdate,'w')-1 AND trunc(sysdate,'w')+5 and DATNas '1900-12-31' and DATAFA = '1900-12-31' and TIPCOL = 1 order by TO_DATE(to_char(DATNas, 'dd/mm') || '/' || to_char(sysdate, 'YYYY'), 'DD/MM/YYYY'), Nomfun"
'response.write cmdListaPessoas.CommandText
cmdListaPessoas.CommandType = 1
Set rsListaPessoas = Server.CreateObject("ADODB.Recordset")
rsListaPessoas.Open cmdListaPessoas, , 3, 3

do while not rsListaPessoas.EOF

    nome = rsListaPessoas("NoMFun")

    nome = Lcase(nome)

    nome = split(nome," ")
    primeironome = nome(LBound(nome))
    ultimonome = nome(UBound(nome))

    primeironome = Ucase(Left(primeironome,1)) & Mid(primeironome,2)
    ultimonome = Ucase(Left(ultimonome,1)) & Mid(ultimonome,2)


    nome = primeironome & " " & ultimonome
    if day(rsListaPessoas("DATNas")) = day(now()) then
        response.write "<b><img src="img/aniversariante.gif"> - " &amp; nome &amp; "</b><br>"
    else
        response.write day(rsListaPessoas("DATNas")) &amp; " - " &amp; nome &amp; "<br>"
    end if


rsListaPessoas.movenext
loop

%>

Segue arquivo ASP anexo…

Attachments:

• Capturar.PNG

[José Laurindo ChiappaModerador]

Ah sim : cfrme a Documentação (https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions255.htm#SQLRF52058) mostra, o W conta a semana ** dentro do mês ** começando no primeiro dia do mês sempre… Se não é isso que vc quer, para que vc considere a semana do Ano, vc pode usa a máscara DAY no TRUNC :

==> mostrando que a máscara W conta a semana dentro do mês, após o dia 1:

SQL> select sysdate, trunc(sysdate,’w’) inicio_semana, trunc(sysdate,’w’)+6 fim_da_semana from dual;

SYSDATE INICIO_SEMANA FIM_DA_SEMANA

---

01/09/2016 01/09/2016 07/09/2016

==> mostrando a máscara DAY :

SQL> select sysdate, trunc(sysdate,’day’) inicio_semana, trunc(sysdate,’day’)+6 fim_da_semana from dual;

SYSDATE INICIO_SEMANA FIM_DA_SEMANA

---

01/09/2016 28/08/2016 03/09/2016

==> como já referido, no padrão comercial/ISO a semana começa na SEGUNDA-FEIRA : pra vc obter o Domingo como primeiro dia no intervalo, tira 1 dia :

SQL> select sysdate, trunc(sysdate,’day’)-1 inicio_semana, trunc(sysdate,’day’)+5 fim_da_semana from dual;

SYSDATE INICIO_SEMANA FIM_DA_SEMANA

---

01/09/2016 27/08/2016 02/09/2016

SQL>

[]s

Chiappa

[José Laurindo ChiappaModerador]

Ops, última forma na minha resposta : a documentação nos avisa que a máscara indicada ** não usa ** ISO, mas sim calendário gregoriano :

“The TRUNC (date) function returns date with the time portion of the day truncated to the unit specified by the format model fmt. This function is not sensitive to the NLS_CALENDAR session parameter. It operates according to the rules of the Gregorian calendar.”

Então desconsidere o que eu falei antes, falha minha…. Na verdade :

SQL> select sysdate, trunc(sysdate,’day’) inicio_semana from dual;

SYSDATE INICIO_SEMANA

---

01/09/2016 28/08/2016

SQL>

=> dia 28 foi um DOMINGO, ele realmente NÂO USA a lógica ISO de começar na segunda-feira não… Aí é mesmo só somar 6 dias :

SQL> select sysdate, trunc(sysdate,’day’) inicio_semana, trunc(sysdate,’day’)+6 fim_da_semana from dual;

SYSDATE INICIO_SEMANA FIM_DA_SEMANA

---

01/09/2016 28/08/2016 03/09/2016

SQL>

legal ?

[]s

Chiappa

[Eder PardeiroParticipante]

Mais uma vez obrigado. Só não entendi como implementar no meu select…. eu adiciono naquele que montamos ou substituo?

Hoje está assim:

SELECT * FROM R034FUN where TO_DATE(to_char(DATNas, 'dd/mm') || '/' || to_char(sysdate, 'YYYY'), 'DD/MM/YYYY') BETWEEN trunc(sysdate,'w')-1 AND trunc(sysdate,'w')+5 and DATNas '1900-12-31' and DATAFA = '1900-12-31' and TIPCOL = 1 order by TO_DATE(to_char(DATNas, 'dd/mm') || '/' || to_char(sysdate, 'YYYY'), 'DD/MM/YYYY'), Nomfun

[José Laurindo ChiappaModerador]

Bem, primeiro antes de responder uma Observação : é uma prática ** danada de ruim ** vc comparar uma coluna DATE ou uma coluna NUMBER com um valor string : entre os muitos problemas que vc pode enfrentar se insistir nessa técnica destrambelhada de programação, um deles é que para converter a string para data o RDBMS vai usar a máscara de data padrão da sessão, que no RDBMS Oracle o usuário PODE MUDAR à vontade, aí se for mudada para alguma coisa não-padrão vc recebe um erro pelas fuças, tipo um ORA-01843: not a valid month ou similar…
Assim sendo, se essa coluna DATNas e essa coluna DATAFA são mesmo do datatype DATE, simplesmente é ** asnino **, é Perigoso de levar falha pela cara se vc programar :

and DATNas ‘1900-12-31’
and DATAFA = ‘1900-12-31’

==> o CORRETO, o método à prova de balas, que ** NUNCA ** vai falhar acontecça o que acpontecer nos settings de data da máquina ou do programa-cliente , é :

and DATNas TO_DATE(‘1900-12-31’, ‘YYYY-MM-DD’)
and DATAFA = TO_DATE(‘1900-12-31’, ‘YYYY-MM-DD’)

legal ? Isso é um procedimento Básico de programação SEGURA no RDBMS Oracle, ie, nunca confiar em conversões de dados implícitas…

Agora a sua resposta : eu não sei bem qual é a sua dúvida depois de tantos exemplos mostrando que o SYSDATE traz a data de hoje e o TRUNC com máscara de semana traz o primeiro dia, mas ao que entendo é simplesmente ** TROCAR ** a máscara W, que é ISO, pela DAY, que ´dia da semana no calendário Gregoriano…
Só ** TROCANDO ** (mesmo!) o TRUNC que vc tinha pelo mesmo exato que indiquei na resposta anterior, fica :

SELECT *
FROM R034FUN
where TO_DATE(to_char(DATNas, ‘dd/mm’) || ‘/’ || to_char(sysdate, ‘YYYY’), ‘DD/MM/YYYY’)
BETWEEN trunc(sysdate,’day’)
AND trunc(sysdate,’day’)+6
and DATNas to_date(‘1900-12-31’, ‘YYYY-MM-DD’)
and DATAFA = to_date(‘1900-12-31’, ‘YYYY-MM-DD’)
and TIPCOL = 1
order by TO_DATE(to_char(DATNas, ‘dd/mm’) || ‘/’ || to_char(sysdate, ‘YYYY’), ‘DD/MM/YYYY’), Nomfun

Usa aí que deve ser isso , pelo que vejo..

[]s

Chiappa

[Autor]

Posts