oracle中add_to_date,ADD_TO_DATE vs Oracle 12c Date Calculations

One could say, you did everything wrong, one could also say you did nothing wrong. Both result dates are wrong.

Here's a simple proof that neither result can be correct:

In the Julian Calendar which was in place throughout most parts of Western Europe in year 1 A.D. every four years a leap day was introduced (you know, that ominous complicated 29th of February). So a sequence of four years in the Julian Calendar (and in the Gregorian Calendar, see below) has a period of 1,461 days.

Now adding 1,916 years to 1.1.0001 means that you add 699,819 days to 1.1.0001, ending on 1.1.1917 (as of the Julian Calendar which is still in use in the Russian Orthodox church and, if I'm not mistaken, in the Greek Orthodox Church). Adding the remaining 171 days (700,000-699,819) ends on June 21st, 1917. Very easy to calculate.

The point is that this calculation on its own is - to say at least - difficult because you're dealing with different calendars here. As far as I know, Oracle and PowerCenter both calculate dates based on the Gregorian Calendar which was inaugurated on Oct 15th, 1583 (after having left out ten days so that the Solar Calendar and the Catholic Calendar would match again). Pretty complex topic, I suggest you read the Wikipedia article. It's quite good and extensive.

In short: when doing date arithmetic, first check that all your parameters are within the defined range of date arithmetic in the underlying system. Informatica, for example, claims (see chapter Datatype Reference in the PowerCenter Designer Guide) that all dates are handled as if the Gregorian Calendar would have been valid since 1.1.0001; obviously (see my calculation above) this is not correct, but I don't intend to discuss this with R&D. I do know that in former versions of PowerCenter the Date/Time values were defined to be handled correctly from 1.1.1753 onward, no earlier.

Personally I would rely on PowerCenter to handle dates from the beginning of the Gregorian Calendar correctly until the year 4916; this year should (according to Pope Gregory XIII's mathematicians) be a leap year, but it is known for some decades by now that the Gregorian Calendar deviates from the Solar Year by a few seconds per year. These few seconds will sum up to one whole day every app. 3,333 years, meaning your 4916 probably will not be a leap year (thus an exception from the Gregorian rule). However, I'm not 100% sure about this, and I won't start a discussion with public authorities about that. :-)

In short: when dealing with date/time values, make sure you perform calculations only within safe boundaries. It is a surprise to me (thanks for your work on that!) that both Oracle and PowerCenter deliver incorrect results here, but well, that's life.

Regards,

Nico