This chapter contains detailed descriptions and extended examples of functions you can use to manipulate date information in PL/SQL programs.

Most applications store and manipulate dates and times. Dates are quite complicated: not only are they highly formatted, but there are myriad rules for determining valid values and valid calculations (leap days and years, national and company holidays, date ranges, etc.). Fortunately, PL/SQL and the Oracle RDBMS provide many ways to handle date information.

PL/SQL provides a true DATE datatype that stores both date and time information. Each date value contains the century, year, month, day, hour, minute, and second. The DATE datatype does not support the storage of fractions of time less than a second in length. The time itself is stored as the number of seconds past midnight. If you enter a date without a time (most applications do not require the tracking of time), the time portion of the database value defaults to midnight (12:00:00 AM). PL/SQL validates and stores dates which fall in the range January 1, 4712 B.C. to December 31, 4712 A.D (in Oracle Server 8.0 and higher, the maximum valid date is December 31, 9999).

Support for a true date datatype is only half the battle. You also need a language that can manipulate those dates in a natural and intelligent manner -- as dates. PL/SQL offers a set of eight date functions for just this purpose, as shown in
Table 12.1
.

With PL/SQL you will never have to write a program which calculates the number of days between two dates. You will not need to write your own utility to figure out the day of the week on which a date falls. This information, and just about anything else you can think of having to do with dates, is immediately available to you through built-in functions. The date functions in PL/SQL all take dates, and, in some cases, numbers, for arguments, and all return date values. The only exception is MONTHS_BETWEEN, which returns a number.

This section describes each date function and includes examples to give you a solid feel for how you can put the function to use in your programs.

NOTE:
In the examples in this chapter, a date contained in single quotation marks is a character string. PL/SQL converts the string to a true date datatype when it applies the function. (This is an implicit conversion.) Date values that are displayed in the format DD-MON-YYYY and are not contained in single quotation marks represent actual date values in the database.

A true date value looks like this in the examples:

12-DEC-1997

A character representation looks like this in the examples:

'12-DEC-1997'

Remember, a date has its own internal storage format and cannot be viewed or entered directly. These examples also assume that the default format mask for dates is DD-MON-YYYY.

ADD_MONTHS is an overloaded function. You can specify the date and the number of months by which you want to shift that date, or you can list the month_shift parameter first and then the date. Both arguments are required.

PL/SQL allows you to perform arithmetic operations directly on date variables. You may add numbers to a date or subtract numbers from a date. To move a date one day in the future, simply add 1 to the date as shown below:

hire_date + 1

You can even add a fractional value to a date. For example, adding 1/24 to a date adds an hour to the time component of that value. Adding 1/(24*60) adds a single minute to the time component, and so on.

If the month_shift parameter is positive, ADD_MONTHS returns a date for that number of months into the future. If the number is negative, ADD_MONTHS returns a date for that number of months in the past. Here are some examples that use ADD_MONTHS:

Move ahead date by three months:

ADD_MONTHS ('12-JAN-1995', 3) ==> 12-APR-1995

Specify negative number of months in first position:

ADD_MONTHS (-12, '12-MAR-1990') ==> 12-MAR-1989

ADD_MONTHS always shifts the date by whole months. You can provide a fractional value for the month_shift parameter, but ADD_MONTHS will always round down to the whole number nearest zero, as shown in these examples:

If you want to shift a date by a fraction of a month, simply add to or subtract from the date the required number of days. PL/SQL supports direct arithmetic operations between date values.

If the input date to ADD_MONTHS does not fall on the last day of the month, the date returned by ADD_MONTHS falls on the same day in the new month as in the original month. If the day number of the input date is greater than the last day of the month returned by ADD_MONTHS, the function sets the day number to the last day in the new month. For example, there is no 31st day in February, so ADD_MONTHS returns the last day in the month:

ADD_MONTHS ('31-JAN-1995', 1) ==> 28-FEB-1995

This is perfectly reasonable. However, what if the input date falls on the last day of the month and the new month has more days in it than the original month? If I shift two months forward from 28-FEB-1994, do I get back 30-APR-1994 (the last day of the month) or 28-APR-1994 (the same day in the new month as in the old month)? The answer is:

ADD_MONTHS ('28-FEB-1994', 2) ==> 30-APR-1995

If you pass to ADD_MONTHS a day representing the last day in the month, PL/SQL always returns the last day in the resulting month, regardless of the number of actual days in each of the months. This quirk can cause problems. I offer a solution in the section entitled
Section 12.2.1, "Customizing the Behavior of ADD_MONTHS"
" later in this chapter.

The LAST_DAY function returns the date of the last day of the month for a given date. The specification is:

FUNCTION LAST_DAY (date_in IN DATE) RETURN DATE

This function is useful because the number of days in a month varies throughout the year. With LAST_DAY, for example, you do not have to try to figure out if February of this or that year has 28 or 29 days. Just let LAST_DAY figure it out for you.

The MONTHS_BETWEEN function calculates the number of months between two dates and returns that difference as a number. The specification is:

FUNCTION MONTHS_BETWEEN (date1 IN DATE, date2 IN DATE)
RETURN NUMBER

The following rules apply to MONTHS_BETWEEN:

If date1 comes after date2, then MONTHS_BETWEEN returns a positive number.

If date1 comes before date2, then MONTHS_BETWEEN returns a negative number.

If date1 and date2 are in the same month, then MONTHS_BETWEEN returns a fraction (a value between -1 and +1).

If date1 and date2 both fall on the last day of their respective months, then MONTHS_BETWEEN returns a whole number (no fractional component).

If date1 and date2 are in different months and at least one of the dates is not a last day in the month, MONTHS_BETWEEN returns a fractional number. The fractional component is calculated on a 31-day month basis and also takes into account any differences in the time component of date1 and date2.

If you detect a pattern here you are right. As I said, MONTHS_BETWEEN calculates the fractional component of the number of months by assuming that each month has 31 days. Therefore, each additional day over a complete month counts for 1/31 of a month, and:

1 divided by 31 = .032258065--more or less!

According to this rule, the number of months between January 31, 1994 and February 28, 1994 is one -- a nice, clean integer. But to calculate the number of months between January 31, 1994 and March 1, 1994, I have to add an additional .032258065 to the difference (and make that additional number negative because in this case MONTHS_BETWEEN counts from the first date back to the second date.

I don't know about you, but I am simply unable to remember the time in Anchorage when it is 3:00 P.M. in Chicago (and I really doubt that a lot of people in Anchorage can convert to Midwest U.S. time). Fortunately for me, PL/SQL provides the NEW_TIME function. This function converts dates (along with their time components) from one time zone to another. The specification for NEW_TIME is:

where date_in is the original date, zone1 is the starting point for the zone switch (usually, but not restricted to, your own local time zone), and zone2 is the time zone in which the date returned by NEW_TIME should be placed.

So, when it was 12:30 in the morning of September 15, 1994 in Chicago, it was 9:30 in the evening of September 14, 1994 in Anchorage.

NOTE:
By the way, I used TO_DATE with a format mask to make sure that a time other than the default of midnight would be used in the calculation of the new date and time. I then used TO_CHAR with another date mask (this one intended to make the output more readable) to display the date and time, because by default PL/SQL will not include the time component unless specifically requested to do so.

The NEXT_DAY function returns the date of the first day after the specified date which falls on the specified day of the week. Here is the specification for NEXT_DAY:

FUNCTION NEXT_DAY (date_in IN DATE, day_name IN VARCHAR2) RETURN DATE

The day_name must be a day of the week in your session's date language (specified by the NLS_DATE_LANGUAGE database initialization parameter). The time component of the returned date is the same as that of the input date, date_in. If the day of the week of the input date matches the specified day_name, then NEXT_DAY will return the date seven days (one full week) after date_in. NEXT_DAY does not return the input date if the day names match.

Here are some examples of the use of NEXT_DAY. Let's figure out the date of the first Monday and Wednesday in 1997 in all of these examples.

The ROUND function rounds a date value to the nearest date as specified by a format mask. It is just like the standard numeric ROUND function, which rounds a number to the nearest number of specified precision, except that it works with dates. The specification for ROUND is as follows:

FUNCTION ROUND (date_in IN DATE [, format_mask VARCHAR2]) RETURN DATE

The ROUND function always rounds the time component of a date to midnight (12:00 A.M.). The format mask is optional. If you do not include a format mask, ROUND rounds the date to the nearest day. In other words, it checks the time component of the date. If the time is past noon, then ROUND returns the next day with a time component of midnight.

The set of format masks for ROUND is a bit different from those masks used by TO_CHAR and TO_DATE. (See
Chapter 14,
Conversion Functions
, for more information on these functions.) The masks are listed in
Table 12.3
.
These same formats are used by the TRUNC function, described later in this chapter, to perform truncation on dates.

The SYSDATE function returns the current system date and time as recorded in the database. The time component of SYSDATE provides the current time to the nearest second. It takes no arguments. The specification for SYSDATE is:

FUNCTION SYSDATE RETURN DATE

SYSDATE is a function without parameters; as a result, it looks like a system-level variable and programmers tend to use it as if it is a variable. For example, to assign the current date and time to a local PL/SQL variable, you would enter the following:

my_date := SYSDATE;

However, SYSDATE is not a variable. When you use SYSDATE, you are calling a function, which executes underlying code.

NOTE:
In Oracle Version 6 and the earliest releases of the Oracle Server, when you called SYSDATE, PL/SQL issued an implicit cursor to the database to get the current date and time, as follows:

SELECT SYSDATE FROM dual;

Because this is no longer the case, you do not need to be as concerned about extra calls to SYSDATE as you would have in earlier releases.

The TRUNC function truncates date values according to the specified format mask. The specification for TRUNC is:

FUNCTION TRUNC (date_in IN DATE [, format_mask VARCHAR2]) RETURN DATE

The TRUNC date function is similar to the numeric FLOOR function discussed in
Chapter 13,
Numeric, LOB, and Miscellaneous Functions
. Generally speaking, it rounds down to the beginning of the minute, hour, day, month, quarter, year, or century, as specified by the format mask.

TRUNC offers the easiest way to retrieve the first day of the month or first day of the year. It is also useful when you want to ignore the time component of dates. This is often the case when you perform comparisons with dates, such as the following:

IF request_date BETWEEN start_date AND end_date
THEN
...

The date component of date_entered and start_date might be the same, but if your application does not specify a time component for each of its dates, the comparison might fail. If, for example, the user enters a request_date and the screen does not include a time component, the time for request_date will be midnight or 12:00 A.M. of that day. If start_date was set from SYSDATE, however, its time component will reflect the time at which the assignment was made. Because 12:00 A.M. comes before any other time of the day, a comparison that looks to the naked eye like a match might well fail.

If you are not sure about the time components of your date fields and variables and want to make sure that your operations on dates disregard the time component, TRUNCate them:

IF TRUNC (request_date) BETWEEN TRUNC (start_date) AND TRUNC (end_date)
THEN
...

TRUNC levels the playing field with regard to the time component: all dates now have the same time of midnight (12:00 A.M.). The time will never be a reason for a comparison to fail.

Here are some examples of TRUNC for dates (all assuming a default date format mask of DD-MON-YYYY):

Without a format mask, TRUNC sets the time to 12:00 A.M. of the same day: