oracle function like days360 in excel

Question

Is there any oracle functions which will return the number of days between two dates like days360 function in excel?

---

DAYS360 : "Returns the number of days between two dates based on a 360-day year (twelve 30-day months), which is used in some accounting calculations. Use this function to help compute payments if your accounting system is based on twelve 30-day months.

If the starting date is the last day of a month, it becomes equal to the 30th of the same month. If the ending date is the last day of a month and the starting date is earlier than the 30th of a month, the ending date becomes equal to the 1st of the next month; otherwise the ending date becomes equal to the 30th of the same month."

Answer 1

In Oracle we can do simple arithmetic on dates. Alternatively, we can turn the difference into an INTERVAL datatype:

SQL> select id
  2         , start_date
  3         , end_date
  4         , (end_date - start_date) as arith_diff
  5         , (end_date - start_date) DAY TO SECOND as ds_interval
  6  from t42
  7  /


    ID START_DATE           END_DATE             ARITH_DIFF DS_INTERVAL
------ -------------------- -------------------- ---------- --------------------
   111 11-FEB-2010 05:44:52 01-MAR-2010 08:10:18 18.1009954 +18 02:25:26.000000
   222 15-APR-2010 10:50:46 19-MAY-2010 01:44:08 33.6203935 +33 14:53:22.000000
   333 01-JUN-2010 00:00:00 31-AUG-2010 00:00:00         91 +91 00:00:00.000000
   444 11-FEB-2010 00:00:00 01-MAR-2010 00:00:00         18 +18 00:00:00.000000

SQL>

edit

"in days360 function it returns 20. in ur code it is 18.10. what i want is a function which should work exactly like days360"

Okay, the Excel function days360() calculates the difference between two dates based on a year of twelve thirty-day months. There is no Oracle built-in which does this. Phil Singer has written his own PL/SQL implementation of this: find it here. Also be sure to read his follow-up piece. Certainly Phil's function does give the results you want:

SQL> select id
  2         , start_date
  3         , end_date
  4         , days360(start_date, end_date) as days360
  5  from t42
  6  /

        ID START_DATE           END_DATE                DAYS360
---------- -------------------- -------------------- ----------
       111 11-FEB-2010 05:44:52 01-MAR-2010 08:10:18         20
       222 15-APR-2010 10:50:46 19-MAY-2010 01:44:08         34
       333 01-JUN-2010 00:00:00 31-AUG-2010 00:00:00         90
       444 11-FEB-2010 00:00:00 01-MAR-2010 00:00:00         20

SQL>