ORA-0000 (SYSDATE Traps)

Quick confession: I never took the 9i TIMESTAMP datatype very seriously. I looked at it merely as a way to record fractional parts of a second. I never needed to record fractional parts of a second.

Then came the Great Daylight Savings Time Scare of 2007. I was glad I never paid attention to TIMESTAMP datatypes. If I had, I might have had a problem. No time zone aware columns, no problem.

Until I wrote a task which was used by all sites in my application to drive cron.

Suddenly, a number of sites starting running tasks repeatedly, starting an hour early. Mysteriously, they all were in the Central time zone.

Here is what the problem was. Cron was using Central time. Even though it was in a data center in the Eastern time zone, the VM server had America/Chicago for its TZ variable. But SYSDATE was returning Eastern time, because it too was in the Eastern time zone, and the listener and spawned dedicated Sql*net servers were all Eastern time. Since my task derived the time from SYSDATE, it was an hour off.

The fix was to replace SYSDATE with CURRENT_DATE. CURRENT_DATE returns the database server time adjusted for the client's time zone. SYSDATE is never adjusted.

A few other functions I should have learned earlier:

DBTIMEZONE: Returns the offset from Zulu time of the database.

SESSIONTIMEZONE: As above, but for the session.

SYSTEMTIMESTAMP: Like SYSDATE, but returns a timestamp with time zone offset

CURRENT_TIMESTAMP: This timestamp is adjusted for the session.

LOCALTIMESTAMP: Like CURRENT_TIMESTAMP, but without the time stamp offset.

PL/SQL Version of DAYS360

While curiously checking where I had contributed to AskTom, I happened to note that someone had responded (a good year after the last post) to a question asking for a PL/SQL function to emulate the Excel days360 function. This brought back so many memories of when I worked in the credit insurance area that I immediately had to write one. For there are many complications in day counting, more than the novice would suspect. Here I am going to repeat the listing, but also discuss a number of issues for which there was not room on the AskTom site. And, before I say anything else, let me point out that this is going to be a very U.S. consumer loan oriented discussion.

Let me state up front that I will only be discussing loans with no compounding of interest, more precisely, where interest is not earned on interest. In practice, this is how most of the consumer loans in the U.S. work. With this assumption, the interest (I) due after one payment interval (T) is the product of the interest rate (R) and the loan balance (A):

  
           I = A * R

For example, if one has a loan of $10,000, and an annual interest rate of 12%, the interest due at the end of the first year is (10000) * (0.12) = $1200.

The interest rate is usually stated as the nominal annual interest rate, since it refers to the interest rate for a period of one year. For those few loans with annual payments (T = one year), this is all one needs. If the payment interval is something other than a year, then one needs to derive the effective interest rate (i) from the nominal rate.

For mortgage and many car loans, the payment interval is one month. Calculating i is then a simple matter of dividing I by 12. To continue our example, if the payment interval were the month instead of the year, the interest due at the end of the first month would be (10000) * (0.12 / 12) = $100.

For credit cards and money market deposits, however, the unit of time is the day, and i is often (but not always!) calculated by dividing I by 365. Why 365? Because there are supposed to be 365 days in a year. And yes, even in 2008, if you check the fine print in your credit card statement, you will probably see that the daily rate is 1/365th of the annual rate.

Oops. We no longer have the payment period equal to the interest interval. If (D) is the number of days between payments, our formula becomes

   I = A * D * (R / 365)

Or, if we go 30 days in our example, I = (10000) * 30 * (0.12 / 365) = $98.63

Of course, if we went 31 days instead, I = (10000) * 31 * (0.12 / 365) = $101.92

Double oops. It looks as if the payee in the above exaple wants to make a monthly payment. Wouldn't it be nice if we could get the interest to be equal to our monthly example of $100? We could if we let the days in a month be 30 (always and everywhere) and changed the 365 to 360.

We finally can describe the wherefore and why of the days360 function. Days360 returns the number of days between two dates, and it tries to do it as if each year had exactly 360 days in it, and each month had exactly 30 days. Of course, eight months do not have 30 days, and there are several reasonable ways to treat those months. The European method (which is invoked by setting the third parameter to 'true') changes the last day of the month to the 30th of the month. The U.S. method (which is invoked by setting the third parameter to 'false') is more complicated, and the Excel help screen should be read if you really want all the details.

Anyway, here is a PL/SQL version of 'days360':

create or replace function days360(
       p_start_date           date,
       p_end_date             date,
       p_rule_type            char default 'F'
       )
    RETURN number
IS
  v_mm1    pls_integer;
  v_dd1    pls_integer;
  v_yyyy1  pls_integer;
  v_mm2    pls_integer;
  v_dd2    pls_integer;
  v_yyyy2  pls_integer;
BEGIN
  v_yyyy1 := to_number(to_char(p_start_date,'yyyy'));
  v_mm1   := to_number(to_char(p_start_date,'mm'));
  v_dd1   := to_number(to_char(p_start_date,'dd'));
  v_yyyy2 := to_number(to_char(p_end_date,'yyyy'));
  v_mm2   := to_number(to_char(p_end_date,'mm'));
  v_dd2   := to_number(to_char(p_end_date,'dd'));
  IF p_rule_type = 'F' THEN
     IF v_dd1 = 31 THEN v_dd1 := 30; END IF;
     IF v_mm1 = 2  AND v_dd1 = to_number(to_char(last_day(p_start_date),'dd'))
          THEN v_dd1 := 30; END IF;
     IF v_dd2 = 31
          THEN IF v_dd1 < 30
                    THEN v_dd2 := 1;
                         v_mm2 := v_mm2 + 1;
                         IF v_mm2 = 13 THEN v_mm2 := 1;
                                            v_yyyy2 := v_yyyy2 +1;
                         END IF;
                    ELSE v_dd2 := 30;
               END IF;
     END IF;
     IF v_mm2 = 2  AND v_dd2 = to_number(to_char(last_day(p_end_date),'dd'))
          THEN v_dd2 := 30;
               IF  (v_dd1 < 30)
                   THEN v_dd2 := 1;
                        v_mm2 := 3;
               END IF;
     END IF;
     IF v_mm2 IN (4, 6, 9, 11) AND v_dd2 = 30
          AND v_dd1 < 30
          THEN v_dd2 := 1;
               v_mm2 := v_mm2 + 1;
     END IF;
  ELSIF p_rule_type = 'T' THEN
     IF v_dd1 = 31 THEN v_dd1 := 30; END IF;
     IF v_dd1 = 31 THEN v_dd1 := 30; END IF;
     IF v_mm1 = 2  AND v_dd1 = to_number(to_char(last_day(p_start_date),'dd'))
          THEN v_dd1 := 30; END IF;
     IF v_dd2 = 31 THEN v_dd2 := 30; END IF;
     IF v_mm2 = 2  AND v_dd2 = to_number(to_char(last_day(p_end_date),'dd'))
          THEN v_dd2 := 30; END IF;
  ELSE RAISE_APPLICATION_ERROR('-20002','3VL Not Allowed Here');
  END IF;
  RETURN (v_yyyy2 - v_yyyy1) * 360
       + (v_mm2 - v_mm1) * 30
       + (v_dd2 - v_dd1);
END;

/

In testing this, I set my NLS_DATE_FORMAT to YYYYMMDD, and I will use that format in this discussion.

Note that days360('20060730', '20070730','T') = days360('20060730', '20070730','T') = 360, as one would expect for a 360 day year. And the same holds true if you change either of the dates to the 31st of the month.

[And the reason I am not using any dates in 2008 is that I want to avoid the complication of leap year]

Now, suppose someone borrows some money on 20060731, and pays the loan back with payments on the last day of each month. In other words, the payment intervals are

  Start Date   Pay Date
   ----------  ---------
   20060731    20060831
   20060831    20060930
   20060930    20061031
   20061031    20061130
   20061130    20061231
   20061231    20070131
   20070131    20070228
   20070228    20070331
   20070331    20070430
   20070430    20070531
   20070531    20070630
   20070630    20070731

If you do the days360 calculation on those payment intervals, you will find that each interval has 30 days, and the 12 intervals in the year add up to 360 days. It seems that the days360 function is doing its job.

Finally, let's assume that someone else takes out an identical loan, but that this person makes some payments early. No payments are later than the last of the month, but some are made earlier. Let's see what happens:

   Start        Pay         European    U.S.
   Date         Date           Days     Days
   -------    ---------    ---------   -------
   20060731    20060830       30         30      
   20060830    20060929       29         29
   20060929    20061030       31         32
   20061030    20061129       29         29
   20061129    20061230       31         32
   20061230    20070128       28         28
   20070128    20070227       29         29
   20070227    20070331       33         34
   20070331    20070429       29         29
   20070429    20070531       31         32
   20070531    20070629       29         29
   20070629    20070731       31         32
                             ====        ===
Totals                        360        365

That's right, you get dinged an extra 5 days interest from trying to pay your loan off early.

Now do you believe there are complications in day counting?