SQL em Oracle > DML > Funções > Funções de linha > Funções para manipular datas
Funções para manipular datas tl_logo2.jpg

Estas funções operam sobre datas Oracle. Todas devolvem um valor do tipo data, excepto MONTHS_BETWEEN que produz um número.


Formato da data:

As datas podem ir de 1 e Janeiro de 4712 AC a 31 de Dezembro de 4712 DC. O ORACLE armazena as datas num formato numérico, que contem 7 bytes. Cada byte armazena um dos componentes abaixo e por esta ordem:

  1. Ano;
  2. Mês;
  3. Dia;
  4. Horas;
  5. Minutos;
  6. Segundos;

A parte inteira do número corresponde ao ano+mês+dia, sendo a parte décimal as horas+minutos+segundos. A unidade de "medida" é o dia.

O formato de output das datas é definido pelos parâmetros descritos nesta página. O comando abaixo altera o formato de apresentação da data para todos os comandos que sejam executados dentro da mesma sessão:

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

A função TO_CHAR() permite alterar o formato de data usado no output de um comando. A cadeia de caracteres usada no resultado segue uma máscara cujos parâmetros estão descritos nesta página.

Temos também disponível uma função para converter de cadeia de caracteres em data, descita nesta página.

bthome.gifTopo


SYSDATE

Devolve a data do servidor onde corre a base de dados. Não necessita argumentos:

select 
  sysdate "Data do servidor", 
  to_char(sysdate,'yyyy-mm-dd hh24-mi-ss') "Data formatada", 
  to_char(sysdate, 'SCC') "Século"
from dual;
Data do servidor          Data formatada      Século 
------------------------- ------------------- ------ 
2006-06-04 13:11:56       2006-06-04 13-11-56  21    

1 rows selected                

bthome.gifTopo


Utilização de operadores aritméticos

Como as datas são armazenadas como números é possivel efectuar cálculos com datas utilizando operadores aritméticos, como a adição e a subtracção. A unidade de armazenamento é o dia.

Operação Significado
Data + número Adiciona um número de dias à data, produzindo nova data
Data - número Subtrai um número de dias à data, produzindo nova data
Data - data Subtrai uma data a outra data, produzindo o número de dias que decorreu entre estas datas
Data + número/24 Adiciona um número de horas, que dividido por 24 dá um número de dias, produzindo uma nova data

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

select hiredate, hiredate+7, round(sysdate-hiredate)
from emp;
alter session set succeeded.

HIREDATE                  HIREDATE+7                ROUND(SYSDATE-HIREDATE) 
------------------------- ------------------------- ----------------------- 
1980-12-17 00:00:00       1980-12-24 00:00:00       9300                    
1981-02-20 00:00:00       1981-02-27 00:00:00       9235                    
1981-02-22 00:00:00       1981-03-01 00:00:00       9233                    
1981-04-02 00:00:00       1981-04-09 00:00:00       9194                    
1981-09-28 00:00:00       1981-10-05 00:00:00       9015                    
1981-05-01 00:00:00       1981-05-08 00:00:00       9165                    
1981-06-09 00:00:00       1981-06-16 00:00:00       9126                    
1982-12-09 00:00:00       1982-12-16 00:00:00       8578                    
1981-11-17 00:00:00       1981-11-24 00:00:00       8965                    
1981-09-08 00:00:00       1981-09-15 00:00:00       9035                    
1983-01-12 00:00:00       1983-01-19 00:00:00       8544                    
1981-12-03 00:00:00       1981-12-10 00:00:00       8949                    
1981-12-03 00:00:00       1981-12-10 00:00:00       8949                    
1982-01-23 00:00:00       1982-01-30 00:00:00       8898                    

14 rows selected

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

select 
  sysdate            "Data",
  sysdate+1          "Mais um dia",
  sysdate+1/24       "Mais uma hora",
  sysdate+1/24/60    "Mais um minuto",
  sysdate+1/24/60/60 "Mais um segundo"
from dual;
alter session set succeeded.

Data                      Mais um dia               Mais uma hora             Mais um minuto            Mais um segundo           
------------------------- ------------------------- ------------------------- ------------------------- ------------------------- 
2006-06-04 13:37:32       2006-06-05 13:37:32       2006-06-04 14:37:32       2006-06-04 13:38:32       2006-06-04 13:37:33       

1 rows selected

bthome.gifTopo


MONTHS_BETWEEN(data1,data2)

Determina o número de meses entre data1 e data2. O resultado pode ser positivo ou negativo.>

select months_between(sysdate,hiredate)
from emp
where months_between(sysdate,hiredate) > 300;
MONTHS_BETWEEN(SYSDATE,HIREDATE) 
-------------------------------- 
305,598994922341696535244922341696535245 
303,502220728793309438470728793309438471 
303,437704599761051373954599761051373955 
302,082865890083632019115890083632019116 
301,115123954599761051373954599761051374 

5 rows selected

select
  months_between(to_date('20060602','yyyymmdd'),to_date('20060102','yyyymmdd')),
  months_between(to_date('20060602','yyyymmdd'),to_date('20060104','yyyymmdd'))
from dual;
MONTHS_BETWEEN(TO_DATE('20060602','YYYYMMDD'),TO_DATE('20060102','YYYYMMDD')) MONTHS_BETWEEN(TO_DATE('20060602','YYYYMMDD'),TO_DATE('20060104','YYYYMMDD')) 
----------------------------------------------------------------------------- ----------------------------------------------------------------------------- 
5                                                                             4,93548387096774193548387096774193548387                                      

1 rows selected

bthome.gifTopo


ADD_MONTHS(data,n)

Adiciona n meses de calendário à data. O número n tem que ser inteiro e pode ser negativo:

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

select hiredate, add_months(hiredate,3),add_months(hiredate,-3)
from emp
where deptno=20;
alter session set succeeded.

HIREDATE                  ADD_MONTHS(HIREDATE,3)    ADD_MONTHS(HIREDATE,-3)   
------------------------- ------------------------- ------------------------- 
1980-12-17 00:00:00       1981-03-17 00:00:00       1980-09-17 00:00:00       
1981-04-02 00:00:00       1981-07-02 00:00:00       1981-01-02 00:00:00       
1982-12-09 00:00:00       1983-03-09 00:00:00       1982-09-09 00:00:00       
1983-01-12 00:00:00       1983-04-12 00:00:00       1982-10-12 00:00:00       
1981-12-03 00:00:00       1982-03-03 00:00:00       1981-09-03 00:00:00       

5 rows selected

bthome.gifTopo


NEXT_DAY(data1,c)

Devolve a data do próximo dia da semana especificado por c e a seguir a data1. O parametro c pode ser um número representando um dia da semana ou uma string (ex: (1,'DOMINGO') (2,'SEGUNDA'). A palavra que designa o dia depende do parâmetro NLS_LANGUAGE que estiver activo. Consulte esta página para ver como modificar este parâmetro.

alter session set nls_language='AMERICAN';
alter session set nls_territory='AMERICA';
select sysdate, next_day(sysdate,'FRIDAY'), next_day(sysdate,6)
from dual;

alter session set nls_language='PORTUGUESE';
alter session set nls_territory='PORTUGAL';
select sysdate, next_day(sysdate,'SEXTA'), next_day(sysdate,6)
from dual;
alter session set succeeded.

alter session set succeeded.

SYSDATE                   NEXT_DAY(SYSDATE,'FRIDAY') NEXT_DAY(SYSDATE,6)       
------------------------- ------------------------- ------------------------- 
03-JUN-06                 09-JUN-06                 09-JUN-06                 

1 rows selected


alter session set succeeded.

alter session set succeeded.

SYSDATE                   NEXT_DAY(SYSDATE,'SEXTA') NEXT_DAY(SYSDATE,6)       
------------------------- ------------------------- ------------------------- 
06.06.03                  06.06.09                  06.06.09                  

1 rows selected

bthome.gifTopo


LAST_DAY(data1)

Devolve o último dia do mês de data1.

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

select sysdate, last_day(sysdate), last_day(to_date('2006-01-01','yyyy-mm-dd'))
from dual;
alter session set succeeded.

SYSDATE                   LAST_DAY(SYSDATE)         LAST_DAY(TO_DATE('2006-01-01','YYYY-MM-DD')) 
------------------------- ------------------------- ------------------------- 
2006-06-03 22:29:35       2006-06-30 22:29:35       2006-01-31 00:00:00       

1 rows selected

bthome.gifTopo


TRUNC(data1);

Devolve a data1 com a hora convertida para as zero horas (12:00 AM). Este comando é muito util quando pretendemos comparar datas que têm horas/minutos/segundos diferentes e apenas nos interessa o ano/mês/dia.

TRUNC(data1,'MONTH');

Devolve o primeiro dia do mês que contém data1.

TRUNC(data1,'YEAR');

Devolve o primeiro dia do ano que contém data1.

TRUNC(data1,'HH24');

Devolve a data (ano, mês, dia) e hora, com a hora no inicio (00 minutos e 00 segundos).

TRUNC(data1,'MI');

Devolve a data (ano, mês, dia e hora) com o minuto no inicio (00 segundos).

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

select 
  to_date('2006-07-17 12:15:45','yyyy-mm-dd hh24:mi:ss')                "Data actual",
  trunc(to_date('2006-07-17 12:15:45','yyyy-mm-dd hh24:mi:ss'))         "Trunc",
  trunc(to_date('2006-07-17 12:15:45','yyyy-mm-dd hh24:mi:ss'),'MONTH') "Trunc Month",
  trunc(to_date('2006-07-17 12:15:45','yyyy-mm-dd hh24:mi:ss'),'YEAR')  "Trunc Year"
from dual;
alter session set succeeded.

Data actual               Trunc                     Trunc Month               Trunc Year                
------------------------- ------------------------- ------------------------- ------------------------- 
2006-07-17 12:15:45       2006-07-17 00:00:00       2006-07-01 00:00:00       2006-01-01 00:00:00       

1 rows selected

bthome.gifTopo


ROUND(data1);

Converte a parte décimal para zero, ou seja, 00 horas, 00 minutos e 00 segundos. Mantém a data se as horas estiverem na primeira metade (antes do meio dia). Passa para o dia seguinte se as horas estiverem na segunda metade.

ROUND(data1,'MONTH');

Devolve o primeiro dia do mês que contém data1 se esta estiver na primeira metade. Devolve o primeiro dia do mês seguinte se data1 estiver na segunda metade do mês.

ROUND(data1,'YEAR');

Devolve o primeiro dia do ano que contém data1 se estiver na primeira metade. Devolve o primeiro dia do ano seguinte se data1 estiver na segunda metade do ano.

ROUND(data1,'HH24');

Mesmo que TRUNC mas com o arredondamento da hora.

ROUND(data1,'MI');

Mesmo que TRUNC mas com arredondamento do minuto.

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

select 
  to_date('2006-07-17 12:15:45','yyyy-mm-dd hh24:mi:ss')                "Data actual",
  round(to_date('2006-07-17 12:15:45','yyyy-mm-dd hh24:mi:ss'))         "Round",
  round(to_date('2006-07-17 12:15:45','yyyy-mm-dd hh24:mi:ss'),'MONTH') "Round Month",
  round(to_date('2006-07-17 12:15:45','yyyy-mm-dd hh24:mi:ss'),'YEAR')  "Round Year"
from dual;
alter session set succeeded.

Data actual               Round                     Round Month               Round Year                
------------------------- ------------------------- ------------------------- ------------------------- 
2006-07-17 12:15:45       2006-07-18 00:00:00       2006-08-01 00:00:00       2007-01-01 00:00:00       

1 rows selected

bthome.gifTopo


Realizado por Turtle Learning ®. Última alteração em 2011-02-26