[saturdayis850]

table AAA
id integer
last_checked_date date

I would like to do a query that displays all of the ID's of records whose "last_checked_date" is equal to or more than 30 days in the past. In other words, if it's been 30 days or more since I last did something with that ID, then give me the ID of that record.

I've tried something like this, but it didn't work. There's probably a simpler solution that will work correctly.
SELECT * FROM AAA a
WHERE ( ADDDATE( a. last_checked_date, INTERVAL 30 DAY ) >= curdate() )


What should the query look like? I've tried a few things, but I haven't gotten it to work yet. I'm using MySQL 3.x, and my web host may be using a higher version, so I'd like to find a standard approach that will work across versions.

Thanks!

[Simon Mundy]

Originally Posted by saturdayis850

table AAA
id integer
last_checked_date date

I would like to do a query that displays all of the ID's of records whose "last_checked_date" is equal to or more than 30 days in the past. In other words, if it's been 30 days or more since I last did something with that ID, then give me the ID of that record.

I've tried something like this, but it didn't work. There's probably a simpler solution that will work correctly.
SELECT * FROM AAA a
WHERE ( ADDDATE( a. last_checked_date, INTERVAL 30 DAY ) >= curdate() )


What should the query look like? I've tried a few things, but I haven't gotten it to work yet. I'm using MySQL 3.x, and my web host may be using a higher version, so I'd like to find a standard approach that will work across versions.

Thanks!

It may be simpler to create the SQL exactly as you described it:-

SELECT * FROM AAA a
WHERE a.last_checked_date <= DATE_SUB(NOW(), INTERVAL 30 DAY)

[madmacgames]

If you'll just be using MySQL, you could also use the MySQL TO_DAYS function. Something like this should work:

Code:
SELECT * from Table WHERE ((TO_DAYS(last_checked_date) - TO_DAYS(NOW()) <= 30);