I recently started using this syntax
SELECT *
FROM table
WHERE cr_date >= TODAY - X UNITS MONTH
I have seen that over the last few days it errors with "datetime
computation out of range"
From some google searches I understand that 31-May - 3 UNITS Month =
31-Feb?

So my question would be that if it is not going to resolve to the last
day of the indicated month and is therefore going to error whats the
point in allowing the function?

On 5/31/10 4:24 PM, Necron99 wrote:
The behaviour was introduced with the DATETIME types in 1990 (OnLine
4.00 and SE 4.00), and has remained the same ever since.

Adding intervals from the year/month class to dates after the 28th of a
month always run the risk of generating an invalid date - the 29th for
February not in a leap year, and the 30th for February in a leap year,
and the 31st for any of September, April, June and November (and
February, of course).

If you really want to get confused, compute the value of
MONTHS_BETWEEN() for pairs of values such as:

2010-03-31 06:00:00 and 2010-02-28 06:00:00
2010-03-31 18:00:00 and 2010-02-28 06:00:00
2010-03-30 06:00:00 and 2010-02-28 06:00:00
2010-03-30 18:00:00 and 2010-02-28 06:00:00
2010-03-29 12:00:00 and 2010-02-28 06:00:00
2010-03-28 12:00:00 and 2010-02-28 06:00:00

The results are the same as what Oracle produces - but not readily
explainable.

-=JL=-