CODEWITHSUNDEEP
mysqlsql
Maj.jad
Maj.jad

Reputation: 71

Compare two dates in MySQL

I have records in mysql database and every record has expired date so I want to select expired records from my database this is the contents of my table:

+-------------+-------------+----------------------+
| username    | attribute   | value                |
+-------------+-------------+----------------------+
| ali         | Expiration  | 22 Jan 2018 01:01:30 |
| jon         | Expiration  | 10 Jan 2018 02:02:55 |
| goerge      | Expiration  | 22 Feb 2018 12:03    |    
+-------------+-------------+----------------------+

As you can see I have 2 Expired Accounts and when I trying to select these expired Records I have NULL results.

This is my Query:

SELECT *
FROM radcheck
WHERE DATE_FORMAT(STR_TO_DATE(e.value, '%d %M %y'), '%d %M %y')
  <= DATE_FORMAT(NOW(), '%d %M %y');

but the query result show this records 

+-------------+-------------+----------------------+
| username    | attribute   | value                |
+-------------+-------------+----------------------+
| ali         | Expiration  | 22 Jan 2018 01:01:30 |
| jon         | Expiration  | 10 Jan 2018 02:02:55 |
| goerge      | Expiration  | 22 Feb 2018 12:03    |
+-------------+-------------+----------------------+

Even if the second record (jon) is not expired yet. Where is the mistake?

Upvotes: 1

Views: 2150

Answers (3)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522731

You should call STR_TO_DATE with the complete format string:

SELECT *
FROM radcheck
WHERE STR_TO_DATE(value, '%d %b %y %h:%i:%s') <= CURDATE();

But the best long term solution is to not store your dates as text. Instead, use a date type column for date information.

Demo

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133400

You should convert the string in a proper date and get the date part 

select * from radcheck 
where  date(STR_TO_DATE(e.value, '%d %b %y %T'))  <= curdate()

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You should fix your data structure so dates are stored as dates. In a key-value store, you should use the standard date/time formats, so they are readily converted.

In any case, you should be doing the comparison using dates not strings:

select *
from radcheck
where str_to_date(e.value, '%d %M %y') <= curdate();

Note that this uses curdate() rather than now(). I doubt the current time is something that you need to consider.

Upvotes: 0

Related Questions