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.
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 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);
All suggestions welcomed.
John.
