List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:October 7 1999 10:41am
Subject:Re: Can I rely on date_add to land on a valid date.
View as plain text  
On Thu, 1999-10-07 17:58:44 +0800, John Hennessy wrote:
> If the date in question is '1999-01-31' and I do the following...
> 
> update table SET Due=DATE_ADD('1999-01-31', INTERVAL 1 MONTH)
> 
> The new date in February truncates drops back to the 28th which is
> what I want.  Can I rely on this behavior in my calculations.

Yes, this is the specified and documented behaviour.

The relevant paragraph in chapter "7.3.11 Date and time functions" of
the MySQL Reference Manual reads:
| If you add MONTH, YEAR_MONTH or YEAR and the resulting date has a
| day that is larger than the maximum day for the new month, the day
| is adjusted to the maximum days in the new month.


> On this same topic I want use the following table to store a clients
> joining date, billing period and due date...
> 
> +--------+---------+------+-----+------------+-------+
> | Field  | Type    | Null | Key | Default    | Extra |
> +--------+---------+------+-----+------------+-------+
> | User   | char(8) |      | PRI |            |       |
> | Start  | date    |      |     | 0000-00-00 |       |
> | Period | int(2)  |      |     | 0          |       |
> | Due    | date    |      |     | 0000-00-00 |       |
> +--------+---------+------+-----+------------+-------+
> 
> I am adding the "Period" (1, 3, 6, 12) months to the "Start" date
> initially to calculate the "Due" date.
> 
> From here I am wanting to bump the newly calculated "Due" dayofmonth
> forward to it's original "Start" dayofmonth where the month allows
> it.  This is how I see it working but am unsure of the syntax to
> achieve it ...
> 
> 1) Add the "Period" to the "Due" date.   ( ie. 1 month)
> 2) Use the "Start" dayofmonth as the "Due" dayofmonth.
> 3) Rely on MySQL to truncate the dayofmonth if it exceeds month
>    dayofmonth range.
> 
> I need something like this that works or is there a simpler way...
> 
> update table SET Due=DATE_ADD('1999-01-31', INTERVAL 1 MONTH),
> (DAYOFMONTH(Due) = DAYOFMONTH(Start);

As far as I see it, you don't have to touch the dayofmonth seperately,
because adding an interval of some months doesn't change the
dayofmonth!

Examples demonstrate this easily:
 DATE_ADD('1999-01-31', INTERVAL  1 MONTH) ==> 1999-02-28
 DATE_ADD('1999-01-31', INTERVAL  3 MONTH) ==> 1999-04-30
 DATE_ADD('1999-01-31', INTERVAL  6 MONTH) ==> 1999-07-31
 DATE_ADD('1999-01-31', INTERVAL 12 MONTH) ==> 2000-01-31

Or:
 DATE_ADD('1999-01-29', INTERVAL  1 MONTH) ==> 1999-02-28
 DATE_ADD('1999-01-29', INTERVAL  3 MONTH) ==> 1999-04-29
 DATE_ADD('1999-01-29', INTERVAL  6 MONTH) ==> 1999-07-29
 DATE_ADD('1999-01-29', INTERVAL 12 MONTH) ==> 2000-01-29

Or:
 DATE_ADD('1999-01-01', INTERVAL  1 MONTH) ==> 1999-02-01
 DATE_ADD('1999-01-01', INTERVAL  3 MONTH) ==> 1999-04-01
 DATE_ADD('1999-01-01', INTERVAL  6 MONTH) ==> 1999-07-01
 DATE_ADD('1999-01-01', INTERVAL 12 MONTH) ==> 2000-01-01

Or did I miss your point?

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=5244 5EF3 B0B1 3826  E4EC 8058 7B31 3AD7
Thread
Can I rely on date_add to land on a valid date.John Hennessy7 Oct
  • Re: Can I rely on date_add to land on a valid date.Martin Ramsch7 Oct