CODEWITHSUNDEEP
sqloracle-databasestored-proceduresoracle11g
sdrunjul
sdrunjul

Reputation: 11

Stored procesure Exact number of days between two dates

I need a store procedure for Oracle 11g whom calculate the exact number of days between two dates, taking in count that some months have 28 days and others 30.

I also have a problem managing leap years. Any idea to fill the procedure body?

CREATE OR REPLACE FUNCTION days(p_from_date IN DATE, p_to_date IN DATE)
RETURN NUMBER

 -- Insert your code here!

END;

Thanks in advance.

Upvotes: 0

Views: 1196

Answers (2)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60292

Oracle dates already take leap years into account. Just do a simple subtraction:

RETURN p_to_date - p_from_date;

Upvotes: 1

Jörj Svenssen
Jörj Svenssen

Reputation: 83

I use this one, that works perfect:

CREATE OR REPLACE FUNCTION days(p_from_date IN DATE, p_to_date IN DATE)
   RETURN NUMBER IS
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' THEN
    b_days := b_days - 1;
  END IF;

  IF TO_CHAR(p_from_date,'D') = '1' THEN
    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')) from dual;

DAYS(TO_DATE('01/11/2007','DD/MM/YYYY'),TO_DATE('06/11/2007','DD/MM/YYYY'))
---------------------------------------------------------------------------
                                                                          4

Upvotes: 0

Related Questions