7-2. Adding Some Number of Days to a Date
Problem
You want to add a number of days to a given date. For example, you are developing an application that calculates shipping dates for a company's products. In this case, your application is processing shipments, and you need to calculate a date that is 14 days from the current date.
Solution
Treat the number of days as an integer, and add that integer to your DATE value. The following lines of code show how this can be done:
DECLARE
ship_date DATE := SYSDATE + 14;
BEGIN
DBMS_OUTPUT.PUT_LINE('The shipping date for any products '||
'that are ordered today is ' || ship_date);
END;
The result that is displayed for this example will be 14 days past your current date.
If you wanted to encapsulate this logic within a function, then it would be easy to do. The following function takes a date and a number as arguments. The function will perform simple mathematics and return the result.
CREATE OR REPLACE FUNCTION calculate_days(date_to_change IN DATE,
number_of_days IN NUMBER)
RETURN DATE IS
BEGIN
RETURN date_to_change + number_of_days;
END;
Notice that the name of the function does not include the word add, such as ADD_DAYS. That was done on purpose because this function not only allows addition of days to a date, but if a negative number is passed in as an argument, then it will also subtract the number of days from the given date.
How It Works
Since calculations such as these are the most common date calculations performed, Oracle makes them easy to do. If a number is added to or subtracted from a DATE value, Oracle Database will add or subtract that number of days from the date value. DATE types can have numbers added to them, and they can also have numbers subtracted from them. Multiplication and division do not work because it is not possible to perform such a calculation on a date. For example, it doesn't mean anything to speak of multiplying a date by some value.
If you are developing an application that always performs an addition or subtraction using the same number of days, it may be helpful to create a function such as the one demonstrated in the solution to this recipe. For instance, if you were developing a billing application and always required a date that was 30 days into the future of the current date, then you could create a function named BILLING_DATE and hard-code the 30 days into it. This is not necessary, but if your business or application depended upon it, then it may be a good idea to encapsulate logic to alleviate possible data entry errors.