Page 177
I purposefully used the last day of the month to illustrate an important fact. Unlike NEXT_DAY, which will add one week if the day of the week specified is the same as the input date, the LAST_DAY function will always return the last day of the month even if the input date is the same.
You can take this one step further and see how many days of summer exist in the month of June by subtracting the last day of the month by the start date of summer. Execute Listing 7.28 to see the result.
INPUT
Listing 7.28. Calculating the number of days of summer
in June.
1: SELECT LAST_DAY('20-JUN-97') "Last_Day",
2: LAST_DAY('20-JUN-97') - TO_DATE('20-JUN-97') "Days_Summer"
3: from DUAL;
OUTPUT
Your output will be
Last_Day Days_Summer --------- ----------- 30-JUN-97 10
This function returns the number of months between two given dates. If the day is the same in both months, you will get an integer value returned. If the day is different, you will get a fractional result based upon a 31-day month. If the second date is prior to the first date, the result will be negative.
The Syntax for the MONTHS_BETWEEN Function
MONTHS_BETWEEN(input_date1,input_date2)
You can see all the possible returned values by executing the code in Listing 7.29.
INPUT
Listing 7.29. Experimenting with MONTHS_BETWEEN.
1: SELECT MONTHS_BETWEEN('25-DEC-97','02-JUN-97') "Fractional",
2: MONTHS_BETWEEN('02-FEB-97','02-JUN-97') "Integer"
3: from DUAL;
OUTPUT
Your output will be
Fractional Integer ---------- ------- 6.7419355 -4
Page 178
| TIP |
Who cares about seeing the fractional part of a 31-day month? To convert the fraction to days, simply multiply the TRUNC value of the fractional part by 31 to convert to days. If you want to display the month, use TRUNC on this value. |
Have you ever wondered what time it was in Germany? Would the phone call be waking the person up in the middle of the night? The NEW_TIME function enables you to find out the time in the time zones listed in Table 7.10 by simply passing the date and time of the first zone, and specifying the second zone.
The Syntax for the NEW_TIME Function
NEW_TIME(input_date and time,time_zone1,time_zone2)
What are the valid time zones? See Table 7.10.
Table 7.10. Time zones.
| Time Zone Abbreviation Passed | Time Zone Description |
| AST | Atlantic Standard Time |
| ADT | Atlantic Daylight Saving Time |
| BST | Bering Standard Time |
| BDT | Bering Daylight Saving Time |
| CST | Central Standard Time |
| CDT | Central Daylight Saving Time |
| EST | Eastern Standard Time |
| EDT | Eastern Daylight Saving Time |
| GMT | Greenwich Mean Time (Date Line!) |
| HST | Alaska-Hawaii Standard Time |
| HDT | Alaska-Hawaii Daylight Saving Time |
| MST | Mountain Standard Time |
| MDT | Mountain Daylight Saving Time |
| NST | Newfoundland Standard Time |
| PST | Pacific Standard Time |
Page 179
| Time Zone Abbreviation Passed | Time Zone Description |
| PDT | Pacific Daylight Saving Time |
| YST | Yukon Standard Time |
| YDT | Yukon Daylight Saving Time |
You can compute the date and time difference between Chicago and Los Angeles by specifying Central Daylight Time to Pacific Daylight Time. Enter and execute the code in Listing 7.30.
INPUT
Listing 7.30. Time change from Chicago to Los Angeles.
1: SELECT TO_CHAR(NEW_TIME(TO_DATE(`060297 01:00:00 AM', 2: `MMDDYY HH:MI:SS AM'), 3: `CDT','PDT'), `DD-MON-YY HH:MI:SS AM') "Central to Pacific" 4: from DUAL;
| TIP |
Remember, minutes are expressed as MI, not MM. This is a common mistake! |
OUTPUT
Your output will be
Central to Pacific ---------------------- 01-JUN-97 11:00:00 PM
ANALYSIS Because there is a two-hour time difference, you not only see the revised time, but the revised date as well. I guess you truly can go back in time!
| TIP |
In a database that traverses time zones, you might want to store the time and date for all entries in one standardized time zone, along with the time zone abbreviation from the original time zone. This will save you a lot of time and coding when designing the database. |
Page 180
ROUND is very similar to the TRUNC function. In fact, it uses the same format mask as TRUNC did in Table 7.9. This function enables you to round up or down based upon the format mask. The default mask when specifying a DATE value is DD. Some useful purposes for this are
The Syntax for the ROUND Function
ROUND(input_date and time or number,rounding_specification)
You can practice rounding to the nearest minute to charge people who use cellular phones by entering the code in Listing 7.31.
INPUT
Listing 7.31. Rounding to the nearest minute.
1: SELECT TO_CHAR(ROUND(TO_DATE(`060297 01:00:35 AM', 2: `MMDDYY HH:MI:SS AM'), 3: `MI'), `DD-MON-YY HH:MI:SS AM') "Rounded to nearest Minute" 4: from DUAL;
OUTPUT
Your output will be
Rounded to nearest Minute -------------------------- 02-JUN-97 01:01:00 AM 10
ANALYSIS Because the seconds were 30 or greater, this example rounded to the next minute at 1:01 from 1:00. Had the number of seconds been 22, the return value would be 1:00. You should test this on your own.
Today, you discovered only a fraction of Oracle's powerful built-in function. Today's lesson stressed the importance of converting data and working with dates. I highly recommend that you refer to Appendix B to review the rest of the functions. A final huge tip: Punctuation is very important!
Q Are all functions available from within PL/SQL?
A No. There are several functions that can be used in SQL only. Refer to Appendix B.