sql - Stored procesure Exact number of days between two dates -
i need store procedure oracle 11g whom calculate exact number of days between 2 dates, taking in count months have 28 days , others 30.
i have problem managing leap years. idea fill procedure body?
create or replace function days(p_from_date in date, p_to_date in date) return number -- insert code here! end;
thanks in advance.
i use one, works perfect:
create or replace function days(p_from_date in date, p_to_date in date) return number b_days number; begin b_days := trunc(p_to_date) - trunc(p_from_date) - ((trunc(p_to_date,'d')-trunc(to_date(p_from_date),'d'))/7)*2 + 1; if to_char(p_to_date,'d') = '7' b_days := b_days - 1; end if; if to_char(p_from_date,'d') = '1' b_days := b_days - 1; end if; return(b_days); end;
/
function created. sql> select days(to_date('01/11/2007','dd/mm/yyyy'),to_date('06/11/2007','dd/mm/yyyy')) dual; days(to_date('01/11/2007','dd/mm/yyyy'),to_date('06/11/2007','dd/mm/yyyy')) --------------------------------------------------------------------------- 4
Comments
Post a Comment