Jeff Smelser wrote:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
On Monday 07 June 2004 10:03 pm, starofframe wrote:
but delete from table where date_sub(crea_dt, interval 1 day)<crea_dt should delete all data right??? All I want is compared to date_now.... eg : now is 2004-6-8 ....and all I want to delete is data that hav passed 1 or more days... such as L 2004-6-7,2004-6-6, etc..
Here: http://dev.mysql.com/doc/mysql/de/Date_and_time_functions.html
I gave you the function.. You can figure out how more to use it.
If crea_dt is a DATE, there's no sense comparing it to NOW(). Use CURDATE() instead. If crea_dt is a DATETIME, use CURDATE() or NOW() according to your specifications. For example, the following will delete rows dated yesterday or earlier:
DELETE FROM table WHERE crea_dt < CURDATE();
This next will delete rows from the day before yesterday or earlier:
DELETE FROM table WHERE crea_dt < CURDATE() - INTERVAL 1 DAY;
If crea_dt is a DATETIME, and you want to select/delete rows as soon as they are over 24 hours old, use:
DELETE FROM table WHERE crea_dt < NOW() - INTERVAL 1 DAY;
On a separate note, you generally shouldn't put the column in a function in your WHERE clause, as it prevents the use of an index on that column. Rather, compare the column to a function which evaluates to a constant. In other words,
WHERE crea_dt < NOW() - INTERVAL 1 DAY
can use an index on crea_dt, but
WHERE crea_dt + INTERVAL 1 DAY < NOW()
cannot.
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
