Implement where condition varchar field in MySQL

Question

I have usages table which contains field values like below

id | page_open_time

1, 28 May 2019 08:39:11
2, 12 July 2019 18:39:11
3, 22 December 2019 11:39:11
4, 23 June 2019 12:39:11

Now I would like to get all records where page_open_time is greater than 1 july, 2019. For this, I wrote this query below , but it didnt help. Is there any way, I can perform this ?

 SELECT * FROM `usages` where page_open_time > '2019-07-01'

Fix your data model so you are storing date/time values as `datetime` rather than as a string. — Gordon Linoff, Dec 27 '19 at 11:59
Does this answer your question? [Mysql Compare two datetime fields](https://stackoverflow.com/questions/1058031/mysql-compare-two-datetime-fields) — mozilla-firefox, Dec 27 '19 at 12:02

score 1 · Accepted Answer · answered Dec 27 '19 at 12:06

Your dates are not in a valid MySQL DATETIME format, so you need to convert them before comparison, using STR_TO_DATE:

SELECT *
FROM usages
WHERE STR_TO_DATE(page_open_time, '%d %M %Y %H:%i:%s') > '2019-07-01'

Output

id  page_open_time
2   12 July 2019 18:39:11
3   22 December 2019 11:39:11

Demo on dbfiddle

Implement where condition varchar field in MySQL

1 Answers1