Ryan,
Try to use this date functions.
* DATE_ADD(date,INTERVAL expr type) , DATE_SUB(date,INTERVAL expr type)
These functions perform date arithmetic. date is a DATETIME or DATE value
specifying the starting date. expr is an expression specifying the interval
value to be added or subtracted from the starting date. expr is a string;
it may start with a '-' for negative intervals. type is a keyword
indicating how the expression should be interpreted.
The INTERVAL keyword and the type specifier are not case sensitive.
The following table shows how the type and expr arguments are related:
type Value Expected expr Format MICROSECOND MICROSECONDS SECOND SECONDS
MINUTE MINUTES HOUR HOURS DAY DAYS WEEK WEEKS MONTH MONTHS QUARTER QUARTERS
YEAR YEARS SECOND_MICROSECOND 'SECONDS.MICROSECONDS' MINUTE_MICROSECOND
'MINUTES.MICROSECONDS' MINUTE_SECOND 'MINUTES:SECONDS' HOUR_MICROSECOND
'HOURS.MICROSECONDS' HOUR_SECOND 'HOURS:MINUTES:SECONDS' HOUR_MINUTE
'HOURS:MINUTES' DAY_MICROSECOND 'DAYS.MICROSECONDS' DAY_SECOND 'DAYS
HOURS:MINUTES:SECONDS' DAY_MINUTE 'DAYS HOURS:MINUTES' DAY_HOUR 'DAYS
HOURS' YEAR_MONTH 'YEARS-MONTHS'
The type values DAY_MICROSECOND, HOUR_MICROSECOND, MINUTE_MICROSECOND,
SECOND_MICROSECOND, and MICROSECOND are allowed as of MySQL 4.1.1. The
values QUARTER and WEEK are allowed as of MySQL 5.0.0.
MySQL allows any punctuation delimiter in the expr format. Those shown
in the table are the suggested delimiters. If the date argument is a DATE
value and your calculations involve only YEAR, MONTH, and DAY parts (that
is, no time parts), the result is a DATE value. Otherwise, the result is a
DATETIME value.
As of MySQL 3.23, INTERVAL expr type is allowed on either side of the +
operator if the expression on the other side is a date or datetime value.
For the - operator, INTERVAL expr type is allowed only on the right side,
because it makes no sense to subtract a date or datetime value from an
interval. (See examples below.)
mysql> SELECT '1997-12-31 23:59:59' + INTERVAL 1 SECOND;
-> '1998-01-01 00:00:00'
mysql> SELECT INTERVAL 1 DAY + '1997-12-31';
-> '1998-01-01'
mysql> SELECT '1998-01-01' - INTERVAL 1 SECOND;
-> '1997-12-31 23:59:59'
mysql> SELECT DATE_ADD('1997-12-31 23:59:59',
-> INTERVAL 1 SECOND);
-> '1998-01-01 00:00:00'
mysql> SELECT DATE_ADD('1997-12-31 23:59:59',
-> INTERVAL 1 DAY);
-> '1998-01-01 23:59:59'
mysql> SELECT DATE_ADD('1997-12-31 23:59:59',
-> INTERVAL '1:1' MINUTE_SECOND);
-> '1998-01-01 00:01:00'
mysql> SELECT DATE_SUB('1998-01-01 00:00:00',
-> INTERVAL '1 1:1:1' DAY_SECOND);
-> '1997-12-30 22:58:59'
mysql> SELECT DATE_ADD('1998-01-01 00:00:00',
-> INTERVAL '-1 10' DAY_HOUR);
-> '1997-12-30 14:00:00'
mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
-> '1997-12-02'
mysql> SELECT DATE_ADD('1992-12-31 23:59:59.000002',
-> INTERVAL '1.999999' SECOND_MICROSECOND);
-> '1993-01-01 00:00:01.000001'
If you specify an interval value that is too short (does not include
all the interval parts that would be expected from the type keyword), MySQL
assumes that you have left out the leftmost parts of the interval value.
For example, if you specify a type of DAY_SECOND, the value of expr is
expected to have days, hours, minutes, and seconds parts. If you specify a
value like '1:10', MySQL assumes that the days and hours parts are missing
and the value represents minutes and seconds. In other words, '1:10'
DAY_SECOND is interpreted in such a way that it is equivalent to '1:10'
MINUTE_SECOND. This is analogous to the way that MySQL interprets TIME
values as representing elapsed time rather than as time of day.
If you add to or subtract from a date value something that contains a
time part, the result is automatically converted to a datetime value:
mysql> SELECT DATE_ADD('1999-01-01', INTERVAL 1 DAY);
-> '1999-01-02'
mysql> SELECT DATE_ADD('1999-01-01', INTERVAL 1 HOUR);
-> '1999-01-01 01:00:00'
If you use really malformed dates, the result is NULL. 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:
mysql> SELECT DATE_ADD('1998-01-30', INTERVAL 1 MONTH);
-> '1998-02-28'
Thank you,
At 07:47 PM 11/16/2005, Petr Chardin wrote:
http://bugs.mysql.com/bug.php?id=8523
Ehrwin C. Mina
9/F Tower 2
RCBC Plaza
6819 Ayala Avenue cor. Sen. Gil J. Puyat Avenue
Makati City 1200 Philippines
Cell (63 918) 930 4383
Tel/Fax (63 2) 757 2633
Email [EMAIL PROTECTED]
Web www.chikka.com
This message and any attachment are confidential and may be privileged or
otherwise protected from disclosure. If you are not the intended recipient,
you must not copy this message or attachment or disclose the contents to
any other person. If you have received this transmission in error, please
notify the sender immediately and delete the message and any attachment
from your system. Chikka does not accept liability for any omissions or
errors in this message which may arise as a result of E-Mail-transmission
or for damages resulting from any unauthorized changes of the content of
this message and any attachment thereto. Chikka does not guarantee that
this message is free of viruses and does not accept liability for any
damages caused by any virus transmitted therewith.
