Quando falamos em dados obrigatoriamente nos deparamos nas formas em que estas informações são apresentadas, e principalmente o tipo de informação que vamos manipular. Um dado do tipo DATE no Oracle, sempre se refere a uma data, mas como podemos trabalhar este dado para cada situação, e ainda, fazer operações com ela?
O que vamos falar nesse artigo!
Funções de data no Oracle
Neste artigo, você vai aprender quais são as principais funções de data no Oracle e como um Expert utiliza elas no dia-a-dia. Essas funções estão dentro da linguagem SQL.
Função SYSDATE
A principal função e mais utilizada, é a função SYSDATE, não recebe nenhum parâmetro e de acordo com o formato definido na sessão, retorna a data e hora atual. Bem fácil não é mesmo?
Função TO_CHAR para Datas
A função TO_CHAR transforma qualquer tipo de dado em texto, quando aplicamos esta função em um dado do tipo DATE, podemos manipular nossa saída de dados de diversas formas, as possibilidades são diversas.
Por exemplo, como podemos retornar o ano de contratação de um funcionário?
Neste caso utilizamos o ‘YYYY‘ para dizer ao Oracle que queremos apenas o ano correspondente à data, com 4 dígitos.
Na função to_char utilizamos D para dia, M para Mês e Y para ano.
Com isso temos um leque de possibilidades que vão de acordo com cada necessidade:
TO_CHAR Mês-Ano
TO_CHAR Dia/Mês
Muito útil, mas podemos ir ainda mais além! E se quisermos converter uma data, para um texto por extenso, assim como são escritas em documentos formais?
Neste caso utilizamos a cláusula fmMonth para identificar o nome do mês, e em seguida configuramos o parâmetro NLS_DATE_LANGUAGE para o idioma português. Incrível não é mesmo?
Função TO_DATE
A função TO_DATE converte um determinado texto em uma data válida para o Oracle. Ao contrário do exemplo anterior, imagine uma situação em que temos um campo texto com a data escrita da seguinte forma : “21 de abril de 2015”, e precisamos retornar esta data com o acréscimo de 15 dias.
Sabemos que Oracle não poderia somar 15 dias a partir de um texto, pois este tipo de operação só pode ser feita com dados do tipo DATA ou NUMBER. Desta forma, primeiro convertemos o texto em data, para depois somar 15 dias.
No último exemplo vimos que para acrescentar dias à uma data, basta somar a quantidade de dias, mas e para subtrair datas, dias, somar horas, somar ou subtrair meses no Oracle? Acompanhe os seguintes exemplos:
Subtraindo dias de uma data no Oracle
Subtrai um número de dias à data, gerando uma nova data.
Fazendo Subtrações de Datas no Oracle
Subtrai uma data de outra data, gerando a quantidade de dias entre elas.
Adiciona horas em uma data no Oracle.
Data + dias/24
Função ADD_MONTHS
Um erro comum é imaginar que para se somar um mês à uma data, basta somarmos 30 dias. O resultado estaria errado, pois devemos considerar que um mês pode ter mais, ou menos que 30 dias.
E para isso que serve a função ADD_MONTHS. Com esta função podemos somar ou subtrair meses de uma data sem nos preocupar com a quantidades de dias que os meses podem ter.
Para subtrair meses, basta utilizar números negativos no segundo parâmetro da função ADD_MONTHS.
Função MONTHS_BETWEEN
Já vimos que uma data menos a outra nos retorna a diferença em dias, mas imagine que precisamos gerar um relatório com a quantidade de tempo em que o funcionário trabalha na empresa em meses.
A função MONTHS_BETWEEN é utilizada para calcular a quantidade de meses entre duas datas, se aplica perfeitamente à situação descrita. Utilizaremos ela para calcular a quantidade de meses entre a data de contratação e a data atual!
Utilizamos a função TRUNC para cortar os decimais da diferença e obter os meses arredondados, e ao final concatenamos com a palavra ‘Meses’, para dar mais clareza ao relatório.
Função LAST_DAY
A função LAST_DAY retorna o último dia do mês de uma determinada data.
No exemplo abaixo, utilizamos ela para retornar o último dia do mês da data de contratação do funcionário.
Função NEXT_DAY
Imagine que cada funcionário receba seu salário na segunda sexta-feira de cada mês. Como podemos calcular em que dia caiu ou cairá o pagamento no mês atual?
Para isso utilizaremos a função NEXT_DAY.
A função NEXT_DAY retorna o próximo dia da semana definido por parâmetro. Sendo:
1 – Domingo
2 – Segunda-Feira
3 – Terça-Feira
4 – Quarta-Feira
5 – Quinta-Feira
6 – Sexta-Feira
7 – Sábado.
Neste caso utilizamos primeiramente a função TRUNC para arredondar o dia do mês para o primeiro dia.
Depois utilizamos pela primeira vez a função NEXT_DAY para obter a próxima sexta-feira depois do primeiro dia, e depois novamente a função NEXT_DAY, para se obter a segunda sexta-feira, retornando a data 11/12/2015, que é a segunda sexta-feira do mês 12/2015!
Conclusão
Estas funções vão te ajudar a desenvolver códigos mais complexos no seu dia-a-dia, e com certeza a desempenhar um trabalho de excelência para manipular datas no Oracle.
Lembrando que o número de variações e possibilidades para os exemplos deste artigo são inimagináveis, e podem ser aplicados para todo tipo de situação.
O Oracle possui dezenas de funções de data, sabendo utiliza-las da melhor maneira, podemos obter resultados incríveis! Se você quiser ser um DBA ou um Programador, você precisa dominar todos os tipos de funções dentro do Oracle.
Agora eu gostaria de saber o que você achou desse artigo! Deixe um comentário abaixo me dizendo se você gostou e caso queira saber detalhes sobre alguma função de data que não foi explorada aqui coloque ela no comentário para que possamos escrever um artigo sobre ela!
Link permanente
Link permanente
Para todo desenvolvedor, quando a palavra “Date” é mencionada, já sobe aquele pulguinha na orelha(conversões, possui ou não horas, cálculos, períodos,etc). Toda vez que eu tinha que realizar cálculos com datas, era necessário migrar os dados para outra plataforma e realizar a implementação! Agora, graças a esse artigo, eu sei da existência dessas funções que irão me ajudar muito!
Obrigado Lucas!
Link permanente
Oi Renan, tudo bem?
Muito obrigado pelo seu comentário ficamos muito felizes de ter ajudado você a melhorar os seus conhecimentos
abs
William Miranda
Link permanente
Show lucas.
Muito bom.
Link permanente
O artigo abrange a grande maioria das funções relacionadas a “date“ no banco de dados Oracle, Parabéns Lucas!
Link permanente
Ótimo post, poderia dar algum exemplo de operações com datas para contabilizar horas ?
Obrigado.
Link permanente
Rapaz, isso resolveu todas minhas dúvidas sobre o assunto, obrigado.
Link permanente
Tarcisio,
Ficamos muito felizes mesmo com este seu comentário e esperamos que outros artigos do aprendaplsql possam te ajudar na sua acensão profissional.
abs
William Miranda
Link permanente
Willian poderia explicar por favor a Lógica para obter semestre ,bimestre,trimestre .Por que subtrair 1 da data atual.
SELECT TRUNC((TO_CHAR (sysdate, ‘MM’) – 1) / 3) + 1 AS trimestre FROM DUAL;
Link permanente
Oi Marcia, tudo bem?
Ali você está subtraindo do mês e não da data completa para no caso de ser o mês 3 por exemplo teríamos como resultado( 3/3 = 1 + 1 = 2) e sairia no segundo semestre e a lógica estará errada!
abs
William Miranda
Link permanente
Oi Márcia
Isso você resolve assim:
SELECT CEIL(EXTRACT(MONTH from sysdate)/2) as bimestre FROM DUAL;
SELECT CEIL(EXTRACT(MONTH from sysdate)/3) as trimestre FROM DUAL;
SELECT CEIL(EXTRACT(MONTH from sysdate)/4) as quadrimestre FROM DUAL;
SELECT CEIL(EXTRACT(MONTH from sysdate)/6) as semestre FROM DUAL;
Cordialmente
Patrick Cardoso
Link permanente
Melhor artigo de datas que encontrei na internet. Parabéns!! Me ajudou de verdade.
Link permanente
Bom dia Lucas, muito bom o seu artigo, mas eu tenho uma dúvida (tomara que tu visualize e responda).
Eu preciso pegar informações de uma tabela que pertençam ao intervalo da semana em que eu estou no momento, por exemplo, se eu fizer a consulta no dia de hoje que é 17/07/2017 eu preciso que o intervalo de busca seja entre o dia 16/07 (domingo) até 22/07 (sábado), ou seja
SELECT * FROM tabelaqualquer WHERE data >= 16/07/2017 and data <= 22/07/2017.
Conseguiria me ajudar com isso? Obrigado por enquanto
Link permanente
Oi Matheus, tudo bem?
O Lucas pediu para te responder com o código para fazer essa função funcionar 🙂
select * from tabela x
where x.campo_data between
sysdate + (7-to_char(sysdate,’D’)) – 6 and sysdate + (7-to_char(sysdate,’D’))
abs
William Miranda
Link permanente
Olá Willan, muitíssimo obrigado, deu super certo. Um grande abraço pra vocês