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 …
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
Dostları ilə paylaş: |