Oracle/PLSQL - DateDiff

<< Avant Browser 10.0 Build 029 | Home | MSN Messenger viola la privacy ? >>

Oracle/PLSQL - DateDiff

Ecco una semplice funzione per calcolare la differenza tra due date.

create or replace function FUN_DATEDIFF (
dateFrom in date,
dateTo in date,
interval in char
)
return number
AS
result number;
begin
--
select
    case lower(interval)
      when 'm' then round(MONTHS_BETWEEN(dateTo, dateFrom))
      when 'd' then round(dateTo - dateFrom)
      when 'y' then round(MONTHS_BETWEEN(dateTo, dateFrom)/12)
      else 0
    end into result
from dual;
--
return(result);
--
end FUN_DATEDIFF;

Print | posted on giovedì 21 ottobre 2004 21:05

Comments on this post

# re: Oracle/PLSQL - DateDiff

Prendendo spunto dalla funzione di Raffaele:

create or replace function datediff(p_what in varchar2, p_d1 in date, p_d2 in date) return number is
l_result number;
begin
select decode( upper(p_what), 'SS', (p_d2-p_d1)* 24 * 60 * 60
, 'MI', (p_d2-p_d1)* 24 * 60
, 'HH', (p_d2-p_d1)* 24
, 'DD', trunc(to_number(to_char(to_date(p_d2,'DD-MON-YYYY'), 'j')) - to_number(to_char(to_date(p_d1,'DD-MON-YYYY'), 'j')))

, 'MM', trunc(months_between(to_date(to_char(p_d2,'DD-MON-YYYY'), 'DD-MON-YYYY'), to_date(to_char(p_d1,'DD-MON-YYYY'), 'DD-MON-YYYY')))

, 'YY', trunc(months_between(to_date(to_char(p_d2,'DD-MON-YYYY'), 'DD-MON-YYYY'), to_date(to_char(p_d1,'DD-MON-YYYY'), 'DD-MON-YYYY')) / 12)

, NULL )
into l_result from dual;
return(l_result);
end datediff;

USAGE: select datediff( 'DD', '01-May-1976 00:00:00', '01-May-2004 00:00:00' ) from dual

SS = Secondi
MI = Minuti
HH = Ore
DD = Giorni
MM = Mesi
YY = Anni

Left by Francesco Meani on ott 22, 2004 2:41

# re: Oracle/PLSQL - DateDiff

awsome

Left by rahul on ott 18, 2005 4:47

# re: Oracle/PLSQL - DateDiff

Salve, sto provando l'esempio
select datediff( 'DD', '01-May-1976 00:00:00', '01-May-2004 00:00:00' ) from dual

ma ho sempre l'errore:
ORA-01821: formato data non riconosciuto
01821. 00000 - "date format not recognized"

DATABASE ORACLE NLS: ITALIAN

Left by Marco on mag 20, 2011 10:19

Comments have been closed on this topic.

Beyond the Good and Evil