[ Pobierz całość w formacie PDF ]
.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 thefuture.If the number is negative, ADD_MONTHS returns a date for that number of months in the past.Hereare some examples that use ADD_MONTHS:"Move ahead date by three months:ADD_MONTHS ('12-JAN-1995', 3) ==> 12-APR-1995"12.1 Date Function Descriptions 443 [Appendix A] What's on the Companion Disk?Specify negative number of months in first position:ADD_MONTHS (-12, '12-MAR-1990') ==> 12-MAR-1989ADD_MONTHS always shifts the date by whole months.You can provide a fractional value for themonth_shift parameter, but ADD_MONTHS will always round down to the whole number nearest zero, asshown in these examples:ADD_MONTHS ('28-FEB-1989', 1.5) same asADD_MONTHS ('28-FEB-1989', 1) ==> 31-MAR-1989ADD_MONTHS ('28-FEB-1989', 1.9999) same asADD_MONTHS ('28-FEB-1989', 1) ==> 31-MAR-1989ADD_MONTHS ('28-FEB-1989', -1.9999) same asADD_MONTHS ('28-FEB-1989', -1) ==> 31-JAN-1989ADD_MONTHS ('28-FEB-1989',.5) same asADD_MONTHS ('28-FEB-1989', 0) ==> 28-FEB-1989If you want to shift a date by a fraction of a month, simply add to or subtract from the date the requirednumber 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 byADD_MONTHS falls on the same day in the new month as in the original month.If the day number of theinput date is greater than the last day of the month returned by ADD_MONTHS, the function sets the daynumber to the last day in the new month.For example, there is no 31st day in February, so ADD_MONTHSreturns the last day in the month:ADD_MONTHS ('31-JAN-1995', 1) ==> 28-FEB-1995This is perfectly reasonable.However, what if the input date falls on the last day of the month and the newmonth has more days in it than the original month? If I shift two months forward from 28-FEB-1994, do I getback 30-APR-1994 (the last day of the month) or 28-APR-1994 (the same day in the new month as in theold month)? The answer is:ADD_MONTHS ('28-FEB-1994', 2) ==> 30-APR-1995If you pass to ADD_MONTHS a day representing the last day in the month, PL/SQL always returns the lastday in the resulting month, regardless of the number of actual days in each of the months.This quirk cancause problems.I offer a solution in the section entitled Section 12.2.1, "Customizing the Behavior ofADD_MONTHS"" later in this chapter.12.1.2 The LAST_DAY functionThe 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 DATEThis 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 letLAST_DAY figure it out for you.Here are some examples of LAST_DAY:"Go to the last day in the month:12.1.2 The LAST_DAY function 444 [Appendix A] What's on the Companion Disk?LAST_DAY ('12-JAN-99') ==> 31-JAN-1999"If already on the last day, just stay on that day:LAST_DAY ('31-JAN-99') ==> 31-JAN-1999"Get the last day of the month three months after being hired:LAST_DAY (ADD_MONTHS (hiredate, 3))"Tell me the number of days until the end of the month:LAST_DAY (SYSDATE) - SYSDATE12.1.3 The MONTHS_BETWEEN functionThe MONTHS_BETWEEN function calculates the number of months between two dates and returns thatdifference as a number.The specification is:FUNCTION MONTHS_BETWEEN (date1 IN DATE, date2 IN DATE)RETURN NUMBERThe 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 valuebetween -1 and +1)."If date1 and date2 both fall on the last day of their respective months, then MONTHS_BETWEENreturns 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 a31-day month basis and also takes into account any differences in the time component of date1 anddate2.Here are some examples of the uses of MONTHS_BETWEEN:"Calculate two ends of month, the first earlier than the second:MONTHS_BETWEEN ('31-JAN-1994', '28-FEB-1994') ==> -1"Calculate two ends of month, the first later than the second:12.1.3 The MONTHS_BETWEEN function 445 [Appendix A] What's on the Companion Disk?MONTHS_BETWEEN ('31-MAR-1995', '28-FEB-1994') ==> 13"Calculate when both dates fall in the same month:MONTHS_BETWEEN ('28-FEB-1994', '15-FEB-1994') ==> 0"Perform months_between calculations with a fractional component:MONTHS_BETWEEN ('31-JAN-1994', '1-MAR-1994') ==> -1.0322581MONTHS_BETWEEN ('31-JAN-1994', '2-MAR-1994') ==> -1.0645161MONTHS_BETWEEN ('31-JAN-1994', '10-MAR-1994') ==> -1.3225806If you detect a pattern here you are right.As I said, MONTHS_BETWEEN calculates the fractionalcomponent of the number of months by assuming that each month has 31 days.Therefore, each additional dayover 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 -- anice, clean integer.But to calculate the number of months between January 31, 1994 and March 1, 1994, Ihave to add an additional.032258065 to the difference (and make that additional number negative because inthis case MONTHS_BETWEEN counts from the first date back to the second date.12.1.4 The NEW_TIME functionI don't know about you, but I am simply unable to remember the time in Anchorage when it is 3:00 P.M.inChicago (and I really doubt that a lot of people in Anchorage can convert to Midwest U.S.time).Fortunatelyfor me, PL/SQL provides the NEW_TIME function [ Pobierz całość w formacie PDF ]