Oracle and PL/SQL Recipes: A Problem-Solution Approach

7-5. Determining the Interval Between Two Dates

Problem

You want to determine the number of days between two dates. For example, working on an application to calculate credit card late fees, you are required to determine the number of days between any two given dates. The difference in days between the two dates will produce the number of days that the payment is overdue.

Solution

Subtract the two dates using simple math to find the interval in days. In this solution, the example code subtracts the current date from the due date to obtain the number of days that the payment is past due:

CREATE OR REPLACE FUNCTION find_interval(from_date IN DATE,                                          to_date IN DATE) RETURN NUMBER AS BEGIN   RETURN abs(trunc(to_date) ...