-1

I want to select records of 5 days ago with mysql, but the problem is that my date column is varchar and in the following format yyyy-mm-dd 00:00:00 AM/PM.

How can I convert this date format to mysql date and select records from 5 days back?

my date column name is 'date_time'

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ershad7
  • 35
  • 11
  • 1
    You should not store dates as varchar, you should store them as datetime. That is why that datatype exists. – Sean Lange Sep 27 '17 at 16:18
  • Possible duplicate of [how to convert a string to date in mysql?](https://stackoverflow.com/questions/5201383/how-to-convert-a-string-to-date-in-mysql) – Alberto Martinez Sep 27 '17 at 16:18
  • @SeanLange you are right, its a very old table, at that time I didn't know about mysql default date type – ershad7 Sep 27 '17 at 18:08

1 Answers1

1

This is a job for STR_TO_DATE().

Try this.

 ... WHERE DATE(STR_TO_DATE(date_time,'%Y-%m-%d %h:%i:%s %p')) = CURDATE() - INTERVAL 5 DAY

The '%Y-%m-%d %h:%i:%s %p' DATE_FORMAT() string matches your textual date format.

This will never be fast if you have to search through a large number of rows, because it isn't sargable. It can't exploit an index.

O. Jones
  • 103,626
  • 17
  • 118
  • 172