Can't delete records 1 day old

[rfresh]

I'm having a heck of a time understanding the syntax to delete a record older than one day - based on a datetime field named event. The following syntax does not work - is it because the field is a datetime type and I'm trying to check against a Date type?

Thanks


DELETE FROM Replies WHERE event <= DATE_SUB( CURDATE( ) , INTERVAL 1 DAY )

[oesxyl]

I'm having a heck of a time understanding the syntax to delete a record older than one day - based on a datetime field named event. The following syntax does not work - is it because the field is a datetime type and I'm trying to check against a Date type?

Thanks


DELETE FROM Replies WHERE event <= DATE_SUB( CURDATE( ) , INTERVAL 1 DAY )

try this:

delete from Replies where to_days(event) - to_days(now()) > 1


best regards

[rfresh]

Didn't work - the field is a datetime type - does that make a difference?

[Old Pedant]

oesxyl has the subtraction backwards!

As given it will always produce a value of less than or equal to zero!

Anyway, your code is saying "delete all records that are <= *MIDNIGHT YESTERDAY*".

The easy answer:


DELETE FROM Replies WHERE DATE(event) <= DATE_SUB( CURDATE( ) , INTERVAL 1 DAY )

The DATE() function in MySQL extracts a date-only value from a datetime value.

But oesxyl's version would work, too, if you simply reversed the order of the subtraction.

[oesxyl]

oesxyl has the subtraction backwards!

As given it will always produce a value of less than or equal to zero!

Anyway, your code is saying "delete all records that are <= *MIDNIGHT YESTERDAY*".

The easy answer:


DELETE FROM Replies WHERE DATE(event) <= DATE_SUB( CURDATE( ) , INTERVAL 1 DAY )

The DATE() function in MySQL extracts a date-only value from a datetime value.

But oesxyl's version would work, too, if you simply reversed the order of the subtraction.
thank you, I didn't see that, :)

best regards