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
Declare variables and constants for dates
Use built-in functions to display and modify date values
Perform computations on dates
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:
DATE—This datatype stores a date and a time, resolved to the second. It does not include the time zone. DATE is the oldest and most commonly used datatype for working with dates in Oracle applications.
TIMESTAMP—Time stamps are similar to dates, but with these two key distinctions: (1) you can store and manipulate times resolved to the nearest billionth of a second (9 decimal places of precision), and (2) you can associate a time zone with a time stamp, and Oracle Database will take that time zone into account when manipulating the time stamp.
INTERVAL—Whereas DATE and TIMESTAMP record a specific point in time, INTERVAL records and computes a time duration. You can specify an interval in terms of years and months, or days and seconds.
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:
Use one of the TIMESTAMP types if you need to track time down to a fraction of a second.
You can, in general, use TIMESTAMP in place of DATE. A time stamp that does not contain subsecond precision takes up 7 bytes of storage, just as a DATE datatype does. When your time stamp does contain subsecond data, it takes up 11 bytes of storage.
Use TIMESTAMP WITH TIME ZONE if you need to keep track of the session time zone in which the data was entered.
Use TIMESTAMP WITH LOCAL TIME ZONE if you want the database to automatically convert a time between the database and session time zones.
Use DATE when it’s necessary to maintain compatibility with an existing application written before any of the TIMESTAMP datatypes were introduced.
Use datatypes in your PL/SQL code that correspond to, or are at least compatible with, the underlying database tables. Think twice, for example, before reading a TIMESTAMP value from a table into a DATE variable, because you might lose information (in this case, the fractional seconds and perhaps the time zone).
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.
|Function||Time Zone||Datatype Returned|
|CURRENT_TIMESTAMP||Session||TIMESTAMP WITH TIME ZONE|
|SYSTIMESTAMP||Database server||TIMESTAMP WITH TIME ZONE|
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:
Use TO_CHAR without a format mask. If you do not include a format mask, the string returned by TO_CHAR will be the same as that returned when Oracle Database performs an implicit conversion:
Use TO_CHAR to display the full names of both the day and the month in the date:
Note: The language used to display these names is determined by the NLS_DATE_LANGUAGE setting, which can also be specified as the third argument in the call to TO_CHAR, as in
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.
Use TO_CHAR to display the full names of both the day and the month in the date—but without all those extra spaces in the date-as-string. Oracle Database, by default, pads the string with spaces to match the maximum length of the day or the month. In most situations, you don’t want to include that extra text, and Oracle Database offers a format element modifier, FM, to control blank and zero padding. In the following block, I prefix the format mask with FM and remove the 0 (before 7) and extra spaces after August:
You can also use the format mask to extract just a portion of, or information about, the date, as shown in the following examples:
What quarter is it?TO_CHAR (SYSDATE, 'Q')
- What is the day of the year (1-366) for today’s date?
TO_CHAR (SYSDATE, 'DDD')
- What are the date and time of a DATE variable? (This is a very common requirement, because the default format mask for a date does not include the time component, which means that asking DBMS_OUTPUT.PUT_LINE to display a date leaves out the time.)
You can also use EXTRACT to extract and return the value of a specified element of a date. For example
- What year is it?
- What is the day for today’s date?
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:
Set l_date to today’s date, but with the time set to 00:00:00:
- Get the first day of the month for the specified date:
- Get the first day of the quarter for the specified date:
- Get the first day of the year for the specified date:
Date arithmetic. Oracle Database enables 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 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:
- Move back one hour:
- Move ahead 10 seconds:
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:
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:
Move backward three months:
- 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.
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');
l_date := NEXT_DAY (SYSDATE, 'SATURDAY');
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.
Take the Challenge!
Each PL/SQL 101 article offers a quiz to test your knowledge of the information provided in the article. The quiz questions are shown below and also at PL/SQL Challenge (plsqlchallenge.com), a Website that offers online quizzes for the PL/SQL language. You can read and take the quiz here in Oracle Magazine and then check your answers in the next issue. If, however, you take the quiz at PL/SQL Challenge, you will be entered into a raffle to win an e-book from O’Reilly Media (oreilly.com).
Oracle Database provides a function for returning the date of the last day of the month. It does not, however, provide a function for returning the date of the first day. Which of the following can be used to do this?
Given this declaration section:
which of the following blocks offers an exception section so that after that block is executed, the date and time 2012-01-01 00:00:01 will be displayed on the screen?
Steven Feuerstein's biography and links to more of his Oracle Magazine PL/SQL articles
When invoked with the form of the second argument, is a synonym for . The related function is a synonym for . For information on the argument, see the discussion for .
When invoked with the form of the second argument, MySQL treats it as an integer number of days to be added to .
adds to and returns the result. is a time or datetime expression, and is a time expression.
converts a datetime value from the time zone given by to the time zone given by and returns the resulting value. Time zones are specified as described in Section 5.1.10, “MySQL Server Time Zone Support”. This function returns if the arguments are invalid.
If the value falls out of the supported range of the type when converted from to UTC, no conversion occurs. The range is described in Section 11.1.2, “Date and Time Type Overview”.
Returns the current date as a value in or format, depending on whether the function is used in a string or numeric context.
Returns the current time as a value in or format, depending on whether the function is used in a string or numeric context. The value is expressed in the current time zone.
If the argument is given to specify a fractional seconds precision from 0 to 6, the return value includes a fractional seconds part of that many digits.
Extracts the date part of the date or datetime expression .
returns − expressed as a value in days from one date to the other. and are date or date-and-time expressions. Only the date parts of the values are used in the calculation.
These functions perform date arithmetic. The argument specifies the starting date or datetime value. is an expression specifying the interval value to be added or subtracted from the starting date. is a string; it may start with a for negative intervals. is a keyword indicating the units in which the expression should be interpreted.
The keyword and the specifier are not case sensitive.
The following table shows the expected form of the argument for each value.
The return value depends on the arguments:
if the first argument is a (or ) value, or if the first argument is a and the value uses , , or .
To ensure that the result is , you can use to convert the first argument to .
MySQL permits any punctuation delimiter in the format. Those shown in the table are the suggested delimiters. If the argument is a value and your calculations involve only , , and parts (that is, no time parts), the result is a value. Otherwise, the result is a value.
Date arithmetic also can be performed using together with the or operator:
is permitted on either side of the operator if the expression on the other side is a date or datetime value. For the operator, is permitted only on the right side, because it makes no sense to subtract a date or datetime value from an interval.
If you specify an interval value that is too short (does not include all the interval parts that would be expected from the keyword), MySQL assumes that you have left out the leftmost parts of the interval value. For example, if you specify a of , the value of is expected to have days, hours, minutes, and seconds parts. If you specify a value like , MySQL assumes that the days and hours parts are missing and the value represents minutes and seconds. In other words, is interpreted in such a way that it is equivalent to . This is analogous to the way that MySQL interprets values as representing elapsed time rather than as a time of day.
Because is treated as a string, be careful if you specify a nonstring value with . For example, with an interval specifier of , evaluates to and is treated as 1 hour, 5000 minutes:
To ensure interpretation of the interval value as you expect, a operation may be used. To treat as 1 hour, 5 minutes, cast it to a value with a single fractional digit:
If you add to or subtract from a date value something that contains a time part, the result is automatically converted to a datetime value:
If you add , , or and the resulting date has a day that is larger than the maximum day for the new month, the day is adjusted to the maximum days in the new month:
Date arithmetic operations require complete dates and do not work with incomplete dates such as or badly malformed dates:
Formats the value according to the string.
The following specifiers may be used in the string. The character is required before format specifier characters.
|Abbreviated weekday name (..)|
|Abbreviated month name (..)|
|Month, numeric (..)|
|Day of the month with English suffix (, , , , …)|
|Day of the month, numeric (..)|
|Day of the month, numeric (..)|
|Minutes, numeric (..)|
|Day of year (..)|
|Month name (..)|
|Month, numeric (..)|
|Time, 12-hour ( followed by or )|
|Time, 24-hour ()|
|Week (..), where Sunday is the first day of the week; mode 0|
|Week (..), where Monday is the first day of the week; mode 1|
|Week (..), where Sunday is the first day of the week; mode 2; used with|
|Week (..), where Monday is the first day of the week; mode 3; used with|