1

I have a table column named date_created where I store datetime values in VARCHAR e.g. 16-06-2013 10:49:29

I want to get results through mysql query between tow dates. I am using query like this:

... WHERE date_created BETWEEN '06-08-2013 22:30:18' AND '28-08-2013 22:30:22' ...

This query return results but that result also includes older date records that are between 06 and 28 of every month. I also use < and > but these also did not work.

How can I get results that only include records between '06-08-2013 22:30:18' AND '28-08-2013 22:30:22'

Muhammad Imran Tariq
  • 22,654
  • 47
  • 125
  • 190
  • Why are you storing it in a `varchar`? Use [the correct data type](http://dev.mysql.com/doc/refman/5.7/en/datetime.html). – Matt Johnson-Pint Aug 28 '13 at 18:09
  • Thanks @MattJohnson. I know these datatypes. But I have done a mistake by using VARCHAR for date. Can I convert VARCHAR to TIMESTAMP ? – Muhammad Imran Tariq Aug 28 '13 at 18:11
  • You should do it one time to migrate your data so you can query properly. If you try to do it during the query, you will not be able to build the proper index and will suffer a performance hit. – Matt Johnson-Pint Aug 28 '13 at 18:21
  • http://stackoverflow.com/questions/7090231/how-to-convert-a-varchar-column-type-to-date-type-without-losing-the-dates – Rohit Chemburkar Aug 28 '13 at 18:21

2 Answers2

7

You can use MySQL-function STR_TO_DATE to format strings into DATETIME.

SELECT STR_TO_DATE('06-08-2013 22:30:18', '%d-%m-%Y %H:%i:%s');
# 2013-08-06 22:30:18

I would recommend to reformat all VARCHARs to DATETIMEs with a single UPDATE and fix the code.

Niko Hujanen
  • 743
  • 5
  • 10
1

you need to use the STR_TO_DATE function to convert your date string into a date that can be used for comparisons.

STR_TO_DATE(date_created, '%d-%m-%Y %H:%i:%s') BETWEEN STR_TO_DATE('06-08-2013 22:30:18', '%d-%m-%Y %H:%i:%s') AND STR_TO_DATE('28-08-2013 22:30:22', '%d-%m-%Y %H:%i:%s')
Scott Jungwirth
  • 6,105
  • 3
  • 38
  • 35