Working with Dates in PL/SQLBy Steven Feuerstein
Part 5 in a series of articles on understanding and using PL/SQL
The previous articles in this introductory PL/SQL series focused on working with strings and numbers in PL/SQL-based applications. Without a doubt, strings and numbers are important, but it is certainly a very rare application that does not also rely on dates. You need to keep track of when events occurred, when people were born, and much more.
As a result, you will quite often need to
A date is also a considerably more complex datatype than a string or a number. It has multiple parts (year, month, day, hour, and so on), and there are many rules about what constitutes a valid date. This article gives you all the information you need in order to begin working with dates in your PL/SQL programs.
Dates, Time Stamps, and Intervals in PL/SQL
Most applications require the storage and manipulation of dates and times. Unlike strings and numbers, dates are quite complicated: not only are they highly formatted data, but there are also many rules for determining valid values and valid calculations (leap days and years, daylight saving time changes, national and company holidays, date ranges, and so on).
Fortunately, Oracle Database and PL/SQL provide a set of true date and time datatypes that store both date and time information in a standard internal format, and they also have an extensive set of built-in functions for manipulating the date and time.
There are three datatypes you can use to work with dates and times:
Listing 1 includes example variables whose declaration is based on these datatypes.
Code Listing 1: Declaring DATE, TIMESTAMP, and INTERVAL variables
DECLARE l_today_date DATE := SYSDATE; l_today_timestamp TIMESTAMP := SYSTIMESTAMP; l_today_timetzone TIMESTAMP WITH TIME ZONE := SYSTIMESTAMP; l_interval1 INTERVAL YEAR (4) TO MONTH := '2011-11'; l_interval2 INTERVAL DAY (2) TO SECOND := '15 00:30:44'; BEGIN null; END;
Working with intervals and time stamps with time zones can be very complicated; relatively few developers will need these more advanced features. This article focuses on the core DATE and TIMESTAMP types, along with the most commonly used built-in functions.
Choosing a datatype. With such an abundance of riches, how do you decide which of these date-and-time datatypes to use? Here are some guidelines:
Getting the current date and time. PL/SQL developers often need to retrieve and work with the current date and time. Most developers use the classic SYSDATE function, but Oracle Database now offers several functions to provide variations of this information, as shown in Table 1.
Listing 2 displays the values returned by calls to SYSDATE and SYSTIMESTAMP.
Code Listing 2: Calls to SYSDATE and SYSTIMESTAMP and the returned values
BEGIN DBMS_OUTPUT.put_line (SYSDATE); DBMS_OUTPUT.put_line (SYSTIMESTAMP); DBMS_OUTPUT.put_line (SYSDATE - SYSTIMESTAMP); END; /
Here is the output:
07-AUG-11 07-AUG-11 08.46.16.379000000 AM -05:00 -000000000 00:00:00.379000000
Because I have passed dates and time stamps to DBMS_OUTPUT.PUT_LINE, Oracle Database implicitly converts them to strings, using the default format masks for the database or the session (as specified by the National Language Settings NLS_DATE_FORMAT parameter). A default installation of Oracle Database sets the default DATE format to DD-MON-YYYY. The default TIMESTAMP format includes both the date offset and the time zone offset.
Note that it is possible to perform date arithmetic: I subtract the value returned by SYSTIMESTAMP from the value returned by SYSDATE. The result is an interval that is very close (but not quite equal) to zero.
Converting dates to strings and strings to dates. As with TO_CHAR for numbers, you use another version of the TO_CHAR function to convert a date or a time stamp to a string. And, again as with numbers, Oracle Database offers a large set of format elements to help you tweak that string so it appears exactly as you need it. Here are some examples:
You can also use the format mask to extract just a portion of, or information about, the date, as shown in the following examples:
You can also use EXTRACT to extract and return the value of a specified element of a date. For example
To convert a string to a date, use the TO_DATE or the TO_TIMESTAMP built-in function. Provide the string and Oracle Database returns a date or a time stamp, using the default format mask for the session:
DECLARE l_date DATE; BEGIN l_date := TO_DATE ('12-JAN-2011'); END ;
If the string you provide does not match the default format, Oracle Database will raise an exception:
DECLARE l_date DATE; BEGIN l_date := TO_DATE ('January 12 2011'); END; / ORA-01858: a non-numeric character was found where a numeric was expected
You should not assume that the literal value you provide in your call to TO_DATE matches the default format. What if the format changes over time? Instead, always provide a format mask when converting strings to dates, as in
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:
Date arithmetic. Oracle Database enables you to perform arithmetic operations on dates and time stamps in several ways:
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):
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:
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:
Here are some examples that use these built-in functions:
The second argument must be a day of the week in the date language of your session (specified by NLS_DATE_LANGUAGE), provided as either the full name or the abbreviation. The returned date has the same time component as the date.
Bad Things Happen—Even in Good Programs
Now that you have a solid foundation in working with key datatypes such as strings, numbers, and dates, I will switch focus in the next article of this series to an in-depth introduction to exceptions: how they can be raised and how you can handle them.