php - Filter MySQL Query By DateTime Stored As Varchar

Question

I'm trying to filter a MySQL query by a field that stores datetime values, but uses a varchar data type.

Example data:
16:56:41 01/14/21 GMT

Example code:

SELECT * FROM table_name WHERE STR_TO_DATE(datetime,'%H:%i:%S %m/%d/%y') BETWEEN '00:00:00 01/14/21' AND '23:59:59 01/14/21'"

The query is currently returning nothing at all. However the code below works:

SELECT * FROM table_name

Any thoughts would be much appreciated.

question from:https://stackoverflow.com/questions/65901856/filter-mysql-query-by-datetime-stored-as-varchar

Answer 1

Your use of STR_TO_DATE is correct, but your date literals are off. Fix them, and your query should work:

SELECT *
FROM table_name
WHERE STR_TO_DATE(datetime,'%H:%i:%S %m/%d/%y') >= '2021-01-14' AND
      STR_TO_DATE(datetime,'%H:%i:%S %m/%d/%y') <  '2021-01-15';

The above assumes that you want records occurring on 14th January, 2021, proper. Note that we don't need to even include the H:M:S components and can just use date literals.

It would be best to make your datetime column a proper datetime type column, rather than text. This would avoid the need to use STR_TO_DATE.