Deleting the user from Tuser when the user didnt login within 24 hours

When the authentication code in the Tuser is not null it means the users didnt login the site within 24 hours so i have to delete the rows.

Here is the query which I wrote but it is not deleting correctly

 DELETE FROM tusers WHERE auth_code IS NOT NULL 
AND auth_code !=''
AND STR_TO_DATE(NOW(),'%Y-%m-%d %T')
> (DATE_SUB(STR_TO_DATE(created_date,'%Y-%m-%d  %T'),INTERVAL -1 DAY))

Answers


You are in a better position to determine whether it is right or not by testing and letting us know if it works or not.

But I can tell you that it is more complicated than it needs to be. You can compare dates directly like this:

... where date(now()) > date_sub(created_date, interval 1 day)

Note that if you are going to SUBTRACT a day in order to go back, you should subtract a positive number of days. Subtracting a negative number of days refers to later in time, not earlier.


I got the perfect delete which is working perfectly:

  DELETE FROM tusers WHERE auth_code IS NOT NULL AND auth_code !=''
 AND DATE_SUB(NOW() , INTERVAL 24 HOUR) > STR_TO_DATE(created_date,'%Y-%m-%d %T');

This query deletes the rows by comparing the current date of the system with the created date of the rows which reaches above 24 hours of the current date.


Need Your Help

how to use one core project for a SQL Server 2014 and Azure SQL Database

sql database azure reference azure-sql-database

we want to deploy our Software on different editions of SQL Server. So we made a CoreDB Project, and for every edition (Express, Standard, Enterprise) seperate projects. Each references the CoreDB to

How to select all parent objects into DataContext using single LINQ query?

c# sql-server linq

I am looking for an answer to a specific problem of fetching whole LINQ object hierarchy using single SELECT.