Working with Dates in PL/SQL（PL/SQL中使用日期）(五)

Add a numeric value to or subtract it from a date, as in SYSDATE + 7; Oracle Database treats the number as the number of days.

Add one date to or subtract it from another, as in l_hiredate - SYSDATE.

Use a built-in function to “move” a date by a specified number of months or to another date in a week.

Here are some examples of date arithmetic with a date and a number (assume in all cases that the l_date variable has been declared as DATE):

Set a local variable to tomorrow’s date:

l_date := SYSDATE + 1;

Move back one hour:

l_date := SYSDATE - 1/24;

Move ahead 10 seconds:

l_date := SYSDATE + 10 / (60 * 60 * 24);

When you add one date to or subtract it from another, the result is the number of days between the two. As a result, executing this block:

DECLARE

l_date1 DATE := SYSDATE;

l_date2 DATE := SYSDATE + 10;

BEGIN

DBMS_OUTPUT.put_line (

l_date2 - l_date1);

DBMS_OUTPUT.put_line (

l_date1 - l_date2);

END;

returns the following output:

10

-10

And the following function can be used to compute the age of a person, assuming that the person’s correct birth date is passed as the function’s only argument:

CREATE OR REPLACE FUNCTION

your_age (birthdate_in IN DATE)

RETURN NUMBER

IS

BEGIN

RETURN SYSDATE -

birthdate_in;

END your_age;

Oracle Database offers several built-in functions for shifting a date by the requested amount or finding a date:

ADD_MONTHS—adds the specified number of months to or subtracts it from a date (or a time stamp)

NEXT_DAY—returns the date of the first weekday named in the call to the function

LAST_DAY—returns the date of the last day of the month of the specified date

Here are some examples that use these built-in functions:

Move ahead one month:

l_date := ADD_MONTHS (SYSDATE, 1);

Move backward three months:

l_date := ADD_MONTHS (SYSDATE, -3);

Starting with the last day of January, move ahead one month. Starting from a different date, go back one month. Starting with the last day of February, go back one month. Listing 3 shows three different calls to the ADD_MONTHS function along with the results.

Code Listing 3: Calls to ADD_MONTHS

BEGIN

DBMS_OUTPUT.put_line (

ADD_MONTHS (TO_DATE ('31-jan-2011', 'DD-MON-YYYY'), 1));

DBMS_OUTPUT.put_line (

ADD_MONTHS (TO_DATE ('27-feb-2011', 'DD-MON-YYYY'), -1));

DBMS_OUTPUT.put_line (

ADD_MONTHS (TO_DATE ('28-feb-2011', 'DD-MON-YYYY'), -1));

END;

Here is the output:

28-FEB-11

27-JAN-11

31-JAN-11

You might be surprised at the third date in Listing 3. The first date (28 February) makes perfect sense. There is no 31st day in February, so Oracle Database returns the last day of the month. The second call to ADD_MONTHS moves the date from 27 February to 27 January: exactly one month’s change. But in the third call to ADD_MONTHS, Oracle Database notices that 28 February is the last day of the month, so it returns the last day of the month specified by the second argument.

Find the next Saturday after today’s date:

l_date := NEXT_DAY (SYSDATE, 'SAT');

-- or

l_date := NEXT_DAY (SYSDATE, 'SATURDAY');

The second argument must be a day of the week in the date language of your sessio