CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11g
Hugo Sousa
Hugo Sousa

Reputation: 1926

Subtraction of dates returning 0 in Oracle

I have created the following function:

create or replace function tempo_medio_atendimento_dia return number is
  counter number; 
  last_date date;
  date_diff number;
  type_info varchar(100);
  cursor c1 is select e.data_pedido from encomenda e, funcionario f where e.funcionario.codigo = f.codigo and to_char(e.data_pedido, 'YYYY-MM-DD') = '2015-04-23' order by e.data_pedido;
  begin
    counter := 0.0;    
    for c1_x in c1 loop
      last_date := to_date(c1_x.data_pedido);
      select dump(last_date) into type_info from dual;
      DBMS_OUTPUT.PUT_LINE('TYPE INFO: ' || type_info);
      if c1%rowcount > 1 then 
        DBMS_OUTPUT.PUT_LINE('DATE:' || c1_x.data_pedido || ' - ' || last_date || ' = ' || (to_date(c1_x.data_pedido) - to_date(last_date)) * 24 * 60 * 60);
        select (to_date(c1_x.data_pedido) - to_date(last_date)) * 24 * 60 * 60 into date_diff from dual;
        counter := counter + date_diff;
      end if;
    end loop;
    return counter;
end tempo_medio_atendimento_dia;

The dates are printing correctly and I've checked the c1_x.data_pedido type and it's 12 (DATE), but for some reason when I try to subtract the dates, it returns 0.

select (to_date(c1_x.data_pedido) - to_date(last_date)) * 24 * 60 * 60 into date_diff from dual;

However, if I run the following code directly, it returns 360, as it's supposed.

select (to_date('23/04/2015 10:49:12') - to_date('23/04/2015 10:43:12')) * 24 * 60 * 60 from dual;

As the data type seems to be correct in the function, I can't understand why the subtraction isn't working. Any tip?

Upvotes: 0

Views: 197

Answers (1)

Chris Hep
Chris Hep

Reputation: 1141

The first thing you do in your loop is 

last_date := to_date(c1_x.data_pedido);

That means later when you do the following 

to_date(c1_x.data_pedido) - to_date(last_date)

You are subtracting c1_x.data_pedido - c1_x.data_pedido.

In addition to that, I would suggest not trying to cast a date as a date. I don't know the data type of data_pedido, but last_date is definitely already a date, so the call to TO_DATE is unnecessary.

Upvotes: 2

Related Questions