Oracle PL/SQL Programming, Third Edition

Date/Time Arithmetic

PL/SQL supports arithmetic involving datetime values, numeric values, and interval values. For example, you can add a given number of days to a date, or you can subtract two dates in order to determine the interval of time between them. Date arithmetic prior to the release of Oracle9i is a very simple topic. Oracle9i’s addition of INTERVAL types makes it much more complex.

Traditional Date Arithmetic

Prior to Oracle9i, date arithmetic meant doing one of two things:

• Adding or subtracting numeric values, representing some number of days, from DATE values.

• Subtracting one DATE from another in order to determine the number of days between them.

In Oracle9i this functionality is still available, but only with the DATE datatype.

Tip

You also could (and still can) make use of date functions such as ADD_MONTHS and MONTHS_BETWEEN. We discuss these later in Section 10.4.

Adding and subtracting numeric values

Integer values represent days when doing date arithmetic. To move a date one day into the future, simply add 1 to the date as shown:

hire_date + 1

You can even add a fractional value to a date. Afractional value represents less than one day. You’ll find fractional values most useful when you work in multiples of the values shown in Table 10-4.

Table 10-4. Fractional values in date arithmetic

Value	Represents
1/24	One hour
1/1440	One minute
1/86400	One second

In the following example, 8/24 is added to hire_date to change the time component from midnight to the ...