mysql date comparision is not working unable to find the issue

Question

i have a huge data with dates as string.

column name date1

datatype varchar

the stored data is in this format:14-Mar-2016 05:44:38pm

Now I have split only date from this string like this: 14-03-2016

By using this: DATE_FORMAT(STR_TO_DATE(gr.date1, '%d-%M-%Y'),'%d-%m-%Y')

Now I am trying to compare the date with this query:

SELECT * FROM 
( SELECT date1,DATE_FORMAT(STR_TO_DATE(date1, '%d-%M-%Y'),'%d-%m-%Y') as dateFormatted
 FROM `grabt` ) as mTbl WHERE mTbl.dateFormatted >= '19-01-2016' 
 AND mTbl.dateFormatted <= '25-01-2016'

but it is not working what could be the possible error.?

Answer 1

Learn to use the right types for columns. Perhaps you are stuck with someone else's really bad decision to store date/times as strings. Perhaps you cannot change that. But, within a query, use the right types!

SELECT mTbl.*,
       LEFT(date1, 10) as FormattedDate  -- Is this really necessary?
FROM (SELECT date1,
             STR_TO_DATE(LEFT(date1, 10), '%d-%M-%Y') as thedate
      FROM `grabt`
     ) mTbl
WHERE mTbl.thedate >= '2016-01-19' AND
      mTbl.thedate <= '2016-01-25';

This will do the comparison as dates not as strings.

Answer 2

The timestamp string 14-Mar-2016 05:44:38pm can be converted to a datetime using the STR_TO_DATE() along with the format string %d-%b-%Y %r. We can then obtain only the date portion by wrapping that with DATE(). Have a look here for a demo to see that this works.

SELECT *
FROM
(
    SELECT DATE(STR_TO_DATE(date1, '%d-%b-%Y %r')) AS dateFormatted
    FROM grabt
) AS mTbl
WHERE mTbl.dateFormatted BETWEEN '2016-01-19' AND '2016-01-25'

As Gordon already pointed out, you should ideally be using date types not strings for your date calculations. And by the way, use a valid date string when comparing in your WHERE clause. YYYY-MM-DD is a valid format, e.g. 2016-01-19, but 19-01-2016 is not.