How to convert text to a date?

There is a table in which the time scored as text and has the format "01.12.2019" (done to me)

Need to do BETWEEN the time

Trying to do so

SELECT * FROM order_kassa WHERE DOC_TIME BETWEEN DATE_FORMAT("01.11.2019",'%d.%m.%Y') and DATE_FORMAT("20.11.2019",'%d.%m.%Y')


but not out
April 3rd 20 at 18:50
3 answers
April 3rd 20 at 18:52
Use STR_TO_DATE() instead of DATE_FORMAT, attribute DOC_TIME is also a concern.
PS: Use this field without a conversion function for the comparison does not make sense. In this offense the previous developer chose the wrong data type.
April 3rd 20 at 18:54
SELECT DATE_FORMAT("01.11.2019",'%d.%m.%Y');

20.11.2001
Do not bother? The DATE_FORMAT function is required for the translation date recorded in the standard MySQL format year-month-day to another view. You serve her on a date in the format day-month-year.
To move the date from any format to the standard desired function STR_TO_DATE(), specifying what format the date is translated.
April 3rd 20 at 18:56
as wrote @jess80
Need line doc_time function STR_TO_DATE to convert to a date, and have it compared with date interval in BETWEEN

SELECT *
 FROM order_kassa
 WHERE str_to_date(doc_time, '%d.%m.%Y') BETWEEN '2019-11-01' AND '2019-11-20';


see example - https://dbfiddle.uk/?rdbms=mysql_5.6&fiddle=cde3b3...

Find more questions by tags MySQL