Additional functions

DATE Functions

As you have learned, the DATE data type stores both date and time information including the hour, minute, and second. Oracle provides a seemingly endless multitude of date functions that can transform a date into almost any display format that you could desire. Oracle also provides functions that can convert date values to character and character values to date. We will focus on the date functions that are used most often. These are described in Table 10.8.

Table 10.8

Function	Use/Definition
ADD_MONTHS	Adds the specified number of months to the specified date and returns that date.
CURRENT_DATE	Returns the current date in the session time zone, in a value in the Gregorian calendar of datatype DATE.
CURRENT_TIMESTAMP	Returns the current date and time in the session time zone, in a value of datatype TIMESTAMP WITH TIME ZONE. The time zone offset reflects the current local time of the SQL session.
ROUND	Rounds date values in the same fashion as the function rounds numbers.
SYSDATE	Returns the current system date and time.
TRUNC	Truncates times to midnight of the date specified.

SYSDATE, CURRENT_DATE, and CURRENT_TIMESTAMP Functions

The SYSDATE function returns the current date and time from the computer's operating system. You can select SYSDATE from any table, so in this respect, SYSDATE is a sort of pseudo-column. Like SYSDATE, the CURRENT_DATE and CURRENT_TIMESTAMP functions return values from the computer's operating system. In the first SELECT statement given in SQL Example 10.26, the SYSDATE is selected from the employee table. The second SELECT statement in SQL Example 10.26 shows the values returned for CURRENT_DATE and CURRENT_TIMESTAMP functions from the dual pseudo table. The time returned is accurate to a very small fraction of a second.
/* SQL Example 10.26 */

SELECT SSN, SYSDATE

FROM Employee;
SSN SYSDATE

--------- ---------

981789642 30-SEP-07

890536222 30-SEP-07

890563287 30-SEP-07
SELECT CURRENT_DATE "Date", CURRENT_TIMESTAMP "Date and Time"

FROM Dual;

Date Date and Time

--------- ----------------------------------

30-SEP-07 30-SEP-07 10.39.45.239671 PM -05:00
The first SELECT statement shown above simply demonstrates the ability to select SYSDATE from a table—the SYSDATE value shown has no particular significance. This also shows the standard format for most Oracle systems when returning date values: DD-MON-YY.

Date Arithmetic

Oracle provides the capability to perform date arithmetic. For example, if you add seven (7) to a value stored in a date column, Oracle will produce a date that is one week later than the stored date. Adding 7 is equivalent to adding 7 days to the date. Likewise, subtracting 7 from a stored date will produce a date that is a week earlier than the stored date.

You can also subtract or compute the difference between two date values. Subtracting two date columns will produce the number of days between the two dates. Suppose that a human resources manager needs to know how long the department manager of department 3 has been assigned to manage the department. The query in SQL Example 10.27 produces the desired result table with the length of assignment expressed in days.

/* SQL Example 10.27 */

COLUMN "Manager ID" FORMAT A10;

COLUMN "Last Name" FORMAT A15;

COLUMN "Number Days" FORMAT 99999999999;

SELECT d.ManagerID "Manager ID", LastName "Last Name",

SYSDATE - d.ManagerAssignedDate "Number Days"

FROM Department d JOIN Employee e ON (d.ManagerID = e.EmployeeID)

WHERE d.DepartmentNumber = 3;

Manager ID Last Name Number Days

---------- --------------- ------------

10044 Sumner 1842

Your answer will vary depending on when you execute the query.

ADD_MONTHS Function

The approach taken above computes how long a manager has been assigned to a specific job position in terms of days; however, managers often are more interested in having this value expressed in months or years. Suppose that a human resources manager needs to know the ten-year anniversary dates for current department managers in order to determine if any of the managers are eligible for a service award. You could execute a query that adds 3,650 days (10 years at 365 days/year) to the ManagerAssignedDate column of the department table; however, this type of date arithmetic would fail to take into consideration leap years that have 366 days. The ADD_MONTHS function solves this problem by adding the specified number of months to a specified date. The format of the function is:
ADD_MONTHS(StartDate, NumberOfMonths)
The query in SQL Example 10.28 displays the required 10-year anniversary information.
/* SQL Example 10.28 */

SELECT d.ManagerID "Manager ID", LastName "Last Name",

ManagerAssignedDate "Start Date",

ADD_MONTHS(ManagerAssignedDate, 120) "10 Yr Anniversary"

FROM Department d JOIN Employee e ON (d.ManagerID = e.EmployeeID)

ORDER BY d.DepartmentNumber;

Manager ID Last Name Start Dat 10 Yr Ann

---------- --------------- --------- ---------

23232 Eakin 21-AUG-97 21-AUG-07

23244 Webber 10-JAN-01 10-JAN-11

10044 Sumner 15-SEP-02 15-SEP-12

More rows will be displayed …

ROUND and TRUNC Functions with Dates

The ROUND function rounds dates in the same fashion as it rounds numbers. If the time value stored in a date column is before noon, ROUND returns a value of 12:00:00 (midnight). Any time stored that is exactly noon or later returns a value of 12:00:00 (midnight) the next day. The TRUNC function truncates times to 12:00:00 (midnight) of the date stored in the date column. These functions can be used to prevent Oracle from returning a fraction of a date in a "difference" type of calculation as demonstrated in SQL Example 10.29.
/* SQL Example 10.29 */

COLUMN Name FORMAT A15;

SELECT Name, TO_DATE('25-FEB-08') - SYSDATE "Not Rounded",

TO_DATE('25-FEB-08') - ROUND(SYSDATE) "Rounded"

FROM Dependent

WHERE EmployeeID = '33355';

NAME Not Rounded Rounded

--------------- ----------- ----------

Allen 147.06103 147