Search code examples
mysqldatecastingvarchar

How do I compare dates (varchar) formatted as dd.mm.yyyy in MySQL?

I'd like to be able to select all dates between two dates in a MySQL database where the dates are a varchar in the format dd.mm.yyyy.

Database
date (varchar) | value
------------------------
31.01.2018     | 123
------------------------
28.02.2018     | 456

This does not work

Query
SELECT date,value from Database WHERE date BETWEEN '2018-01-01' AND '2018-01-31'

I know I could use WHERE date LIKE '%.01.2018' for whole months but that is not sufficient.

I could do this in PHP, but don't want to do that, as that will take too long. I guess I could possibly use the CAST function, but not sure how.

What is the best way to do this in MySQL?

Solution

  • You could use STR_TO_DATE():

    WHERE STR_TO_DATE(date, '%d.%m.%Y') BETWEEN '2018-01-01' AND '2018-01-31'