matches the default format. What if the
format changes over time? Instead, always
provide a format mask when converting
strings to dates, as in
Code Listing 3: Calls to ADD_MONTHS
l_date := TO_DATE ('January 12 2011',
'Month DD YYYY');
Date truncation. Use the TRUNC built-in
function to truncate a date to the specified
unit of measure. The most common use of
TRUNC is TRUNC (date)—without any format
mask specified. In this case, TRUNC simply
sets the time to 00:00:00. You can also use
TRUNC to easily obtain the first day in a specified period. Here are some TRUNC examples:
1. Set l_date to today’s date, but with the
time set to 00:00:00:
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 to perform arithmetic operations on
dates and time stamps in several ways:
•;Add a numeric value to or subtract it from
a date, as in S YSDATE + 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):
1. Set a local variable to tomorrow’s date:
10
- 10
l_date1 - l_date2);
END;
returns the following output:
l_date := TRUNC (SYSDATE);
2. Get the first day of the month for the
specified date:
l_date := TRUNC (SYSDATE, 'MM');
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:
3. Get the first day of the quarter for the
specified date:
l_date := TRUNC (SYSDATE, 'Q');
l_date := SYSDATE + 1;
4. Get the first day of the year for the specified date:
2. Move back one hour:
l_date := TRUNC (SYSDATE, 'Y');
l_date := SYSDATE - 1/24;
CREATE OR REPLACE FUNCTION
your_age (birthdate_in IN DATE)
RETURN NUMBER
IS
BEGIN
RETURN SYSDATE -
birthdate_in;
END your_age;
Date arithmetic. Oracle Database enables
Answers to the Challenge
Here are the answers to the PL/SQL Challenge
questions in last issue’s “Working with
Numbers in PL/SQL” article:
Answer 1: The plch_ceil_and_floor function always returns either 1 or 0: 0 if the
number passed to the function is an integer,
1 otherwise.
Answer 2: (a) and (b) are correct; (c) is
incorrect.
For full explanations of both of these
answers, visit plsqlchallenge.com, register or
log in, and click the Closed/ Taken tab in Play a
Quiz, or go to bit.ly/r1SwvP.
3. 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 (
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)
•;NEX T_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:
1. Move ahead one month:
l_date := ADD_MONTHS (SYSDATE, 1);
