Date Range Query

[dweb]

Hi

 

I need to run a query that does a date range search.

 

My dates are stored as the following format

 

Fri Jan 05 2014

 

I know how to run a query when the dates are stored as YYYY-MM-DD but I cannot figure out how to run a query with the format my dates are stored in

 

I can't change the format of the fields as it's part of a bigger system

 

Does anyone have any suggestions?

[Barand]

You can use STR_TO_DATE() to convert that useless format of yours into the correct yyyy-mm-dd format

... WHERE STR_TO_DATE(mydate, '%a %b %d %Y') BETWEEN d1 AND d2

d1 and d2 should be in yyyy-mm-dd format

 

EG

mysql> SELECT STR_TO_DATE('Fri Jan 05 2014', '%a %b %d %Y') as date;
+------------+
| date       |
+------------+
| 2014-01-05 |
+------------+