How would I write this query?

Hello all,

I have a basic table within a database called learner_users, which stores the account details of each user.

How would I write a query to find all of the users who haven't logged in for 100 days. There is a field within the table called last_login.

Thanks.

 

What type is last_login field? It's better to post your create table script here.

Regards
p.p.: I mean date, datetime, timestamp, integer...?

 

Assuming it is any standard date column and not a varchar you could do something like this.

SELECT column_names FROM learner_users 
WHERE  last_login <= DATE_SUB(CURDATE(),INTERVAL 100 DAY);

Code (markup):

 

The last_login field has a data type of datetime.

A typical value in this field for a user would be something like:

"2010-06-11 17:05:07"

Hope this helps.

Thanks in advance

 

Hi,
you can use query, provided by @Jestep
If you don't want to take care about hours,minutes and seconds and compare dates only, just pass last_login in DATE function, so query will looks like:

SELECT column_names FROM learner_users 
WHERE [COLOR="Red"]DATE[/COLOR](last_login) <= DATE_SUB(CURDATE(),INTERVAL 100 DAY);

Code (markup):

otherwise

SELECT column_names FROM learner_users 
WHERE last_login <= DATE_SUB([COLOR="Red"]NOW()[/COLOR],INTERVAL 100 DAY);

Code (markup):

Regards

 

This works great, thanks!