Select mysql date range based on a VARCHAR Date

Question

What I am up to here is fetching some data from mysql table within a week from today. My only challange is that Dates are stored in VARCHAR format D M d Y.

My table stored Date example: Wed Jul 03 2016

My Code is:

    $result = mysqli_query($con," SELECT * FROM `BIMTECH_academy_2016_classes` WHERE STR_TO_DATE(Date, '%D %M %d %Y') > DATE_SUB(NOW(), INTERVAL 1 WEEK) ORDER BY Date,From DESC; ");
    while($row = mysqli_fetch_array($result))
    {
          echo $row['ClassNumber'];
          echo $row['CourseName'];
          echo $row['Date'];
          echo $row['From'];
          echo $row['To'];
    }

http://stackoverflow.com/questions/3296725/parse-date-in-mysql — Marshall Tigerus, Jun 15 '16 at 17:02
What's wrong with your current code? What does `SELECT STR_TO_DATE(Date, '%D %M %d %Y') FROM BIMTECH_academy_2016_classes` show? (Can't you just fix the database column type?) — Álvaro González, Jun 15 '16 at 17:03
You could try using the MySQL **[date_format](http://www.w3schools.com/sql/func_date_format.asp)** function to format your dates. — Ben M., Jun 15 '16 at 17:03
So, how can I convert this date formt (Wed Jul 03 2016) into this (YYYY-MM-DD) using PHP? Any ideas? — Mazen, Jun 15 '16 at 17:36

score 0 · Answer 1 · answered Jun 15 '16 at 17:07

That's... well, a bit sad. Dates should be stored as proper data type because you can query the data without too much overhead. If there's any chance, please do convert those dates to valid date format and change the column type to date or datetime.

In the meantime, you can use MySQL's STR_TO_DATE() function, but since it would be used in WHERE part of the query, then MySQL will always scan the entire table, then internally convert all strings to dates, and only then compare with what you need. If there's a lot of data stored in a table, that can be really slow. Indexing the column won't help either, since MySQL has to fetch and convert all columns anyway before it's able to perform the comparison.

Exactly. At least use a `DATE` and a `TIME` https://dev.mysql.com/doc/internals/en/date-and-time-data-type-representation.html ... I mean not even do that, but at least. — Drew, Jun 15 '16 at 17:42

score 0 · Answer 2 · answered Jun 15 '16 at 18:27

example: just relace @date with your column name and you should get the date returned in format you want.

SET @date = "Sun Jul 10 2016";
SELECT DATE_FORMAT(STR_TO_DATE(@date,"%a %b %d %Y"),"%Y-%m-%d") as formatted_date

more details: DATE_FORMAT,STR_TO_DATE

Select mysql date range based on a VARCHAR Date

2 Answers2