|
|
|
6.3 Date ManipulationDate arithmetic is an important aspect of our day-to-day life. We find the age of a person by subtracting his date of birth from today's date. We compute the date a warranty expires by adding the warranty period to the purchase date. Drivers' license expirations, bank interest calculation, and a host of other things all depend on date arithmetic. It is extremely important for any database to support such common date arithmetic operations. Oracle provides some very good date arithmetic features. Not only can we add and subtract dates, but Oracle also provides a number of other helpful functions for manipulating date values. We discuss these features in detail in this section. Table 6-2 lists various date manipulation functions provided by Oracle SQL.
6.3.1 AdditionAdding two dates doesn't make sense. However, we can add days, months, years, hours, minutes, and seconds to a date to generate a future date and time. The "+" operator allows us to add numbers to a date. The unit of a number added to a date is assumed to be days. Therefore, to find tomorrow's date, we can add 1 to SYSDATE: SELECT SYSDATE, SYSDATE+1 FROM DUAL; SYSDATE SYSDATE+1 --------- --------- 05-OCT-01 06-OCT-01 Any time we add a number to a date, Oracle assumes that the number represents a number of days. Therefore, if we want to add multiples of a day (week, month, year, etc.) to a date, we first need to multiply by a conversion factor. For example, to add one week to today's date, we add 7 (7 days in a week times 1 day) to SYSDATE: SELECT SYSDATE+7 FROM DUAL; SYSDATE+7 --------- 12-OCT-01 Similarly, if we want to add fractions of a day (hour, minute, second) to a date, we first need to convert such fractions into a fractional number of days. Do this by dividing by a conversion factor. For example, to add 20 minutes to the current date and time, we need to add (20 minutes/1,440 minutes in a day) to SYSDATE: SELECT TO_CHAR(SYSDATE,'DD-MON-YY HH:MI:SS'), TO_CHAR(SYSDATE+(20/1440),'DD-MON-YY HH:MI:SS') FROM DUAL; TO_CHAR(SYSDATE,'D TO_CHAR(SYSDATE+(2 ------------------ ------------------ 05-OCT-01 01:22:03 05-OCT-01 01:42:03 Adding months to a date is not as easy as adding weeks, because all months don't have the same number of days—some have 30, some 31, some 28, and at times even 29. To add one month to a date, we need to know how many days that calendar month will have. Therefore, adding months to a date by converting those months to a number of days involves lots of homework, which is error-prone. Fortunately, Oracle does all the homework for us, and provides a built-in SQL function to add months to dates. This function is called ADD_MONTHS, and we call it as follows: ADD_MONTHS (date, number) The syntax elements are:
The following example shows the computation of an employee's biannual review date by using ADD_MONTHS to add six months to the employee's HIRE_DATE: SELECT FNAME, HIRE_DATE, ADD_MONTHS(HIRE_DATE, 6) REVIEW_DATE FROM EMPLOYEE; FNAME HIRE_DATE REVIEW_DA -------------------- --------- --------- John 22-OCT-99 22-APR-00 Notice that in this example the input date and the result date both fall on the 22nd of the month. This would not have happened if we had added 180 days to the input date. ADD_MONTHS is "smart" in one other way too. The following example adds 6 months to 31st December, 1999: SELECT ADD_MONTHS('31-DEC-99',6) FROM DUAL;
ADD_MONTH
---------
30-JUN-00
The ADD_MONTHS function is intelligent enough to know that adding 6 months to 31st December should result in the last day of June. And since the last of June is 30th (not 31st), it returns 30th June, 2000. 6.3.2 SubtractionEven though no other arithmetic operation (addition, multiplication, division) between two dates makes any sense, subtracting one date from another date is a very common and useful operation. The "-" operator allows us to subtract a date from a date, or a number from a date. Subtracting one date from another date returns the number of days between those two dates. Subtracting a number from a date returns a date that number of days in the past. The following example displays the lead time of a set of orders by subtracting the date on which the order was placed (ORDER_DT) from the expected ship date (EXPECTED_SHIP_DT): SELECT ORDER_NBR, EXPECTED_SHIP_DT - ORDER_DT LEAD_TIME
FROM CUST_ORDER;
ORDER_NBR LEAD_TIME
---------- ----------
1001 1
1000 5
1002 13
1003 10
1004 9
1005 2
1006 6
1007 2
1008 2
1009 4
1012 1
1011 5
1015 13
1017 10
1019 9
1021 2
1023 6
1025 2
1027 2
1029 4
Along with subtracting one date from another, we can also subtract a number from a date. For example, subtracting 1 from SYSDATE gives yesterday, and subtracting 7 from SYSDATE yields the same day last week: SELECT SYSDATE, SYSDATE - 1, SYSDATE - 7 FROM DUAL; SYSDATE SYSDATE-1 SYSDATE-7 --------- --------- --------- 05-OCT-01 04-OCT-01 28-SEP-01 Unlike ADD_MONTHS, Oracle doesn't provide a SUBTRACT_MONTHS function. To subtract months from a date, use the ADD_MONTHS function, and pass a negative number as the second parameter: SELECT SYSDATE, ADD_MONTHS(SYSDATE, -6) FROM DUAL; SYSDATE ADD_MONTH --------- --------- 05-OCT-01 05-APR-01 Earlier in this section we saw that subtracting a date from another date returns the number of days between the two dates. There are times when we may want to know the number of months between two dates. Consider that subtracting an employee's HIRE_DATE from SYSDATE yields the number of days of experience the employee has with her employer: SELECT SYSDATE-HIRE_DATE FROM EMPLOYEE;
SYSDATE-HIRE_DATE
-----------------
714.0786
It's better, in most cases, to find the number of months of experience rather than the number of days. We know that dividing the number of days between two dates by 30 won't accurately calculate the number of months between those two dates. Therefore, Oracle provides the built-in SQL function MONTHS_BETWEEN for finding the number of months between two dates. MONTHS_BETWEEN is called as follows: MONTHS_BETWEEN (date1, date2) The syntax elements are:
MONTHS_BETWEEN subtracts date2 from date1. So, if date2 comes later than date1 in the chronology, then MONTHS_BETWEEN will return a negative value. The following example demonstrates two calls to MONTHS_BETWEEN. Both calls use the same two dates, but in different orders. SELECT MONTHS_BETWEEN(SYSDATE,HIRE_DATE)
MONTHS_BETWEEN(HIRE_DATE, SYSDATE)
FROM EMPLOYEE;
MONTHS_BETWEEN(SYSDATE,HIRE_DATE) MONTHS_BETWEEN(HIRE_DATE,SYSDATE)
--------------------------------- ---------------------------------
23.4542111 -23.454218
There is no YEARS_BETWEEN function. To find the number of years between two dates, we can either subtract the two dates to find the number of days and then divide by 365, or use MONTHS_BETWEEN to find the number of months and then divide by 12. Years don't have the same number of days—some have 365 days and others have 366 days. Therefore, it is not accurate to divide the number of days by 365 to get the number of years. On the other hand, all years have 12 months, whether a leap year or not. Therefore, the most accurate way to calculate the number of years between two dates is to use the MONTHS_BETWEEN function to find the number of months and then divide by 12 to get the number of years. 6.3.3 Last Day of the MonthOracle provides a built-in function to get the last day of a month. The function is LAST_DAY,and it's called as follows: LAST_DAY (date) The syntax element is:
LAST_DAY returns the last day of the month containing the input date. For example, to find the last date of the current month, we can use the following SQL statement: SELECT LAST_DAY(SYSDATE) "Next Payment Date" FROM DUAL; Next Paym --------- 31-OCT-01 Sometimes it's useful to be able to determine the first day of a given month; it would be nice if Oracle would provide a FIRST_DAY function. One approach to getting the first day of the month for a given date is to use the following expression: ADD_MONTHS((LAST_DAY(date)+1), -1) This expression finds the last day of the month represented by date. It then adds 1 to get to the first day of the subsequent month, and finally uses ADD_MONTHS with an argument of -1 to go back to the beginning of the month in which we started. The result is the first day of the month in which the given date falls. Other approaches to this problem are possible; this is just one that works well for us. This approach has the advantage of preserving the time component of the date in question. 6.3.4 Next DayOracle provides a built-in function to get the date of the next occurrence of a specified day of the week. The function is NEXT_DAY, and it's called as follows: NEXT_DAY (date, string) The syntax elements are:
To find the date of the next Friday, we can use the following SQL statement: SELECT NEXT_DAY(SYSDATE, 'Friday') "Vacation Start Date" FROM DUAL; Vacation --------- 12-OCT-01 If the specified string is not a valid day of the week, we will get an error: SELECT NEXT_DAY(SYSDATE, 'ABCD') FROM DUAL;
SELECT NEXT_DAY(SYSDATE, 'ABCD') FROM DUAL
*
ERROR at line 1:
ORA-01846: not a valid day of the week
6.3.5 Rounding and Truncating DatesRounding and truncating dates is similar in concept to the rounding and truncating of numbers, but more involved because an Oracle DATE contains date as well as time information. Use the ROUND function to round a date/time value to a specific element; use the TRUNC function to truncate a date/time value to a specific element. Following is the syntax for invoking these two functions: ROUND(date [, format]) TRUNC(date [, format]) The syntax elements are:
The return value depends upon the specified format, which is an optional parameter. If we don't specify a format in the call to ROUND, the function returns a date by rounding the input to the nearest day. If we don't specify a format in the call to TRUNC, that function returns a date by removing the fractional part of the day. When using ROUND and TRUNC to round to the nearest day, or to truncate a date, the functions set the time fields of the return value to the beginning of the returned day, i.e., 12:00:00 AM (00:00:00 in HH24 format). For example: SELECT TO_CHAR(SYSDATE, 'DD-MON-YY HH:MI:SS AM'),
TO_CHAR(ROUND(SYSDATE), 'DD-MON-YY HH:MI:SS AM'),
TO_CHAR(TRUNC(SYSDATE), 'DD-MON-YY HH:MI:SS AM')
FROM DUAL;
TO_CHAR(SYSDATE,'DD-M TO_CHAR(ROUND(SYSDATE TO_CHAR(TRUNC(SYSDATE
--------------------- --------------------- ---------------------
06-OCT-01 07:35:48 AM 06-OCT-01 12:00:00 AM 06-OCT-01 12:00:00 AM
Notice that since the input time (SYSDATE) is before 12 noon, the output of ROUND and TRUNC are the same. However, if the input time were after 12 noon, the output of ROUND and TRUNC would be different, as in the following example. SELECT TO_CHAR(SYSDATE, 'DD-MON-YY HH:MI:SS AM'),
TO_CHAR(ROUND(SYSDATE), 'DD-MON-YY HH:MI:SS AM'),
TO_CHAR(TRUNC(SYSDATE), 'DD-MON-YY HH:MI:SS AM')
FROM DUAL;
TO_CHAR(SYSDATE,'DD-M TO_CHAR(ROUND(SYSDATE TO_CHAR(TRUNC(SYSDATE
--------------------- --------------------- ---------------------
06-OCT-01 05:35:48 PM 07-OCT-01 12:00:00 AM 06-OCT-01 12:00:00 AM
Since the input time is past 12 noon, ROUND returns the beginning of the next day. However, TRUNC still returns the beginning of the input date. This is similar to the rounding and truncating of numbers. When we specify a format as an input to the ROUND and TRUNC functions, things become a bit more involved, but the concepts of rounding and truncating still remain the same. The difference is that the rounding and truncating are now based on the format we specify. For example, if we specify the format as YYYY, the input date will be truncated based on the year, which means that if the input date is before the middle of the year (July 1st), both ROUND and TRUNC will return the first day of the year. If the input date is after July 1st, ROUND will return the first day of the next year, whereas TRUNC will return the first day of the input year. For example: SELECT TO_CHAR(SYSDATE-180, 'DD-MON-YYYY HH24:MI:SS'),
TO_CHAR(ROUND(SYSDATE-180,'YYYY'),'DD-MON-YYYY HH24:MI:SS'),
TO_CHAR(TRUNC(SYSDATE-180,'YYYY'),'DD-MON-YYYY HH24:MI:SS')
FROM DUAL;
TO_CHAR(SYSDATE-180, TO_CHAR(ROUND(SYSDAT TO_CHAR(TRUNC(SYSDAT
-------------------- -------------------- --------------------
09-APR-2001 20:58:33 01-JAN-2001 00:00:00 01-JAN-2001 00:00:00
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'),
TO_CHAR(ROUND(SYSDATE,'YYYY'),'DD-MON-YYYY HH24:MI:SS'),
TO_CHAR(TRUNC(SYSDATE,'YYYY'),'DD-MON-YYYY HH24:MI:SS')
FROM DUAL;
TO_CHAR(SYSDATE,'DD- TO_CHAR(ROUND(SYSDAT TO_CHAR(TRUNC(SYSDAT
-------------------- -------------------- --------------------
06-OCT-2001 20:58:49 01-JAN-2002 00:00:00 01-JAN-2001 00:00:00
Similarly, we can round or truncate a date to a specific month, quarter, week, century, hour, minute, and so forth by using the appropriate format. Table 6-3 lists the formats (and their meanings) that can be used with the ROUND and TRUNC functions.
6.3.6 NEW_TIMELet's say you work in an office in the New York City and want to schedule a video conference with a customer in Los Angeles. If you aren't careful about the time difference between the two cities, you might end up scheduling the meeting at 9:00 A.M. your time. Hopefully, you know that this is not the proper time to call your customer if you really want to make the deal, because it is too early to expect him to be in the office (9:00 A.M. in New York is 6:00 A.M. in Los Angeles). If you need to deal with time zones in the database, Oracle's built-in NEW_TIME function comes to your rescue. It converts a date and time in a given time zone into a date and time in another time zone. Call NEW_TIME as follows: NEW_TIME (date, input_time_zone, output_time_zone) The syntax elements are:
As an example, to find out the time in Los Angeles when it is 9:00 A.M. at New York, you can use the following SQL: ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY HH:MI:SS AM';
Session altered.
SELECT NEW_TIME('11-NOV-01 09:00:00 AM','EST','PST') FROM DUAL;
NEW_TIME('11-NOV-0109
---------------------
11-NOV-01 06:00:00 AM
In this example, EST and PST correspond to Eastern Standard Time and Pacific Standard Time, respectively. 6.3.7 SELECTing Data Based on Date RangesThere are times when we need to SELECT data from a table based on a given date range. Let's say you have been asked to print all orders placed on a given date, say 22-MAY-01. Probably, your immediate response would be a query such as the following: SELECT * FROM CUST_ORDER WHERE ORDER_DT = '22-MAY-01'; no rows selected There's no output. Surprised? Although you know there are orders on 22-MAY-01, this query didn't return any rows. The reason is that ORDER_DT is a DATE column, and contains time as well as date information. On the other hand, the date literal '22-MAY-01' doesn't contain any time information. When you don't specify the time portion in a date literal, the time portion is assumed to be beginning of the day, i.e., 12:00:00 A.M. (or 00:00:00 in 24 hour format). In the CUST_ORDER table, the time components in the ORDER_DT column are other than 12:00:00 A.M. In this case, the correct query to print orders placed on 22-MAY-01 is: SELECT * FROM CUST_ORDER
WHERE ORDER_DT BETWEEN TO_DATE('22-MAY-01 00:00:00','DD-MON-YY HH24:MI:SS')
AND TO_DATE('22-MAY-01 23:59:59','DD-MON-YY HH24:MI:SS');
ORDER_NBR CUST SALES_EMP SALE_PRICE ORDER_DT EXPECTED_ CANCELLED SHIP STATUS
---------- ---- --------- ---------- --------- --------- --------- ---- ---------
1001 1 3 99 22-MAY-01 23-MAY-01 DELIVERED
1005 8 3 99 22-MAY-01 24-MAY-01 DELIVERED
1021 8 7 99 22-MAY-01 24-MAY-01 DELIVERED
The query treats the one day as a range: 22-MAY-01 00:00:00 to 22-MAY-01 23:59:59. Thus, the query returns any order placed at any time during 22-MAY-01. Another way to solve this problem of needing to ignore the time components in a DATE column would be to truncate the date, and then compare the truncated result with the input literal: SELECT * FROM CUST_ORDER
WHERE TRUNC(ORDER_DT) = '22-MAY-01';
ORDER_NBR CUST SALES_EMP SALE_PRICE ORDER_DT EXPECTED_ CANCELLED SHIP STATUS
---------- ---- --------- ---------- --------- --------- --------- ---- ---------
1001 1 3 99 22-MAY-01 23-MAY-01 DELIVERED
1005 8 3 99 22-MAY-01 24-MAY-01 DELIVERED
1021 8 7 99 22-MAY-01 24-MAY-01 DELIVERED
The TRUNC function sets the time portion to the beginning of the day. Therefore, the equality comparison with the date literal '22-MAY-01' returns the expected output. The same result can be achieved by converting ORDER_DT to a character string in a format matching that of the input data. SELECT * FROM CUST_ORDER WHERE TO_CHAR(ORDER_DT,'DD-MON-YY') = '22-MAY-01'; The downside to the approach of using the TRUNC and TO_CHAR functions is that the resulting query cannot make use of any index that happens to be on the ORDER_DT column. This can have significant performance implications. On the other hand, the date range solution, while more complex to code, does not preclude the use of any index on the column in question.
You can use the same techniques shown in this section to SELECT data based on any given date range, even if that range spans more than just one day. 6.3.8 Creating a Date Pivot TableFor certain types of queries, it's helpful to have a table with one row for each date over a period of time. For example, you might wish to have one row for each date in the current year. You can use the TRUNC function in conjunction with some PL/SQL code to create such a table: CREATE TABLE DATES_OF_YEAR (ONE_DAY DATE);
Table created.
DECLARE
I NUMBER;
START_DAY DATE := TRUNC(SYSDATE,'YY');
BEGIN
FOR I IN 0 .. (TRUNC(ADD_MONTHS(SYSDATE,12),'YY') - 1) - (TRUNC(SYSDATE,'YY'))
LOOP
INSERT INTO DATES_OF_YEAR VALUES (START_DAY+I);
END LOOP;
END;
/
PL/SQL procedure successfully completed.
SELECT COUNT(*) FROM DATES_OF_YEAR;
COUNT(*)
----------
365
The DATES_OF_YEAR table is now populated with the 365 days of the year 2001. We can now play with this table to generate various useful lists of dates. Let's say there are two paydays where you work—the 15th of each month and the last day of each month. Use the following query against the DATES_OF_YEAR table to generate a list of all paydays in the year 2001. SELECT ONE_DAY PAYDAY FROM DATES_OF_YEAR WHERE TO_CHAR(ONE_DAY,'DD') = '15' OR ONE_DAY = LAST_DAY(ONE_DAY); PAYDAY --------- 15-JAN-01 31-JAN-01 15-FEB-01 28-FEB-01 15-MAR-01 31-MAR-01 15-APR-01 30-APR-01 15-MAY-01 31-MAY-01 15-JUN-01 30-JUN-01 15-JUL-01 31-JUL-01 15-AUG-01 31-AUG-01 15-SEP-01 30-SEP-01 15-OCT-01 31-OCT-01 15-NOV-01 30-NOV-01 15-DEC-01 31-DEC-01 24 rows selected. Quite often you are told by a government organization that the processing of a document will take "x" number of days. When they say something like that, they usually mean "x" number of working days. Thus, in order to calculate the expected completion date, you need to count "x" days from the current date, skipping Saturdays and Sundays. Obviously, you can't use simple date arithmetic, because simple date subtraction doesn't exclude weekend days. What you can do is use the DATES_OF_YEAR table. For example: SELECT COUNT(*) FROM DATES_OF_YEAR
WHERE RTRIM(TO_CHAR(ONE_DAY,'DAY')) NOT IN ('SATURDAY', 'SUNDAY')
AND ONE_DAY BETWEEN '&d1' AND '&d2';
Enter value for d1: 18-FEB-01
Enter value for d2: 15-MAR-01
old 3: AND ONE_DAY BETWEEN '&d1' AND '&d2'
new 3: AND ONE_DAY BETWEEN '18-FEB-01' AND '15-MAR-01'
COUNT(*)
----------
19
This query counts the number of days between the two dates you enter, excluding Saturdays and the Sundays. The TO_CHAR function with the 'DAY' format converts each candidate date (from the DATES_OF_YEAR table) to a day of the week, and the NOT IN operator excludes the days that are Saturdays and Sundays. Notice the use of the RTRIM function with TO_CHAR. We used RTRIM because TO_CHAR produces the DAY as a nine-character string, with blank padded to the right. RTRIM eliminates those extra spaces. There could be holidays between two dates, and the queries shown in this section don't deal with that possibility. To take holidays into account, you need another table (perhaps named HOLIDAYS) that lists all the holidays in the year. You can then modify the previous query to exclude days listed in the HOLIDAYS table. 6.3.9 Summarizing by a DATE/Time ElementLet's say you want to print a quarterly summary of all your orders. You want to print the total number of orders and total sale price for each quarter. The order table is as follows: SELECT * FROM CUST_ORDER;
ORDER_NBR CUST SALES PRICE ORDER_DT EXPECTED_ CANCELLED SHIP STATUS
---------- ----- ----------- --------- --------- --------- --- -----------
1001 1 3 99 22-MAY-01 23-MAY-01 DELIVERED
1000 1 4 19-JAN-01 24-JAN-01 21-JAN-01 CANCELLED
1002 5 6 12-JUL-01 25-JUL-01 14-JUL-01 CANCELLED
1003 4 5 56 16-NOV-01 26-NOV-01 DELIVERED
1004 4 4 34 18-JAN-01 27-JAN-01 PENDING
1005 8 3 99 22-MAY-01 24-MAY-01 DELIVERED
1006 1 8 22-JUL-01 28-JUL-01 24-JUL-01 CANCELLED
1007 5 1 25 20-NOV-01 22-NOV-01 PENDING
1008 5 1 25 21-JAN-01 23-JAN-01 PENDING
1009 1 5 56 18-MAY-01 22-MAY-01 DELIVERED
1012 1 2 99 22-JAN-01 23-JAN-01 DELIVERED
1011 1 3 19-NOV-01 24-NOV-01 21-NOV-01 CANCELLED
1015 5 3 12-NOV-01 25-NOV-01 14-NOV-01 CANCELLED
1017 4 1 56 16-MAY-01 26-MAY-01 DELIVERED
1019 4 9 34 18-NOV-01 27-NOV-01 PENDING
1021 8 7 99 22-MAY-01 24-MAY-01 DELIVERED
1023 1 1 22-NOV-01 28-NOV-01 24-NOV-01 CANCELLED
1025 5 3 25 20-MAY-01 22-MAY-01 PENDING
1027 5 1 25 21-NOV-01 23-NOV-01 PENDING
1029 1 5 56 18-MAY-01 22-MAY-01 DELIVERED
20 rows selected.
There is no quarter column in the CUST_ORDER table. You have to manipulate the ORDER_DT column to generate the quarter. The following SQL statement does this using the TO_CHAR function along with a date format. In addition to being used in the SELECT list, notice that TO_CHAR is used in the GROUP BY clause to group the results by quarter. SELECT 'Q'||TO_CHAR(ORDER_DT, 'Q') QUARTER, COUNT(*), SUM(NVL(SALE_PRICE,0)) FROM CUST_ORDER GROUP BY 'Q'||TO_CHAR(ORDER_DT, 'Q'); QU COUNT(*) SUM(NVL(SALE_PRICE,0)) -- ---------- ---------------------- Q1 4 158 Q2 7 490 Q3 2 0 Q4 7 140 Using this same technique, you can summarize data by week, month, year, hour, minute, or any other date/time unit that you choose. |
|
|
|
