Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Im working on a database that store dates in a varchar(10) mysql field (so sad).

I can not alter the database structure (im building a small plug-in), but i have to query the database finding rows where this data field is between the next 10 days.

Example:

| fid | fdate      |
|  1  | 10/09/2010 |
|  2  | 17/09/2010 |
|  3  | 19/09/2010 |
I have to get the rows with fid 1 and 2, becose the date is <= now + 10 days.
Usually i make this kind of query:
SELECT fid FROM table WHERE fdate <= DATE_ADD(NOW(), INTERVAL 10 DAY);
Or, if the date is in the format `yyyymmdd':
SELECT fid FROM table WHERE fdate <= DATE_FORMAT(NOW(), '%Y%m%d');
But theyre useless with the format dd/mm/yyyy.
I've figured out with
SELECT fid, CONCAT(SUBSTRING(fdate, 7, 4), SUBSTRING(fdate, 4, 2), SUBSTRING(fdate, 1, 2)) AS mydate FROM table HAVING mydate <= DATE_ADD(NOW(), INTERVAL 10 DAY);
but i guess it is a bit an overkill rebuilding the date format with concat and substring, and the having clause wont help the query speed.
Any idea?
After Adriano's comment, the right solution is
SELECT fid FROM test WHERE STR_TO_DATE(fdate, '%d/%m/%Y') <= DATE_ADD(NOW(), INTERVAL 10 DAY);
so i can avoid the concat and having clause.
                You can use where instead of having, but otherwise you appear to have solved the problem-- so what exactly are you asking?
– Andomar
                Sep 7, 2010 at 14:03
                In the last query i've posted, i cant use WHERE instead of HAVING, becose the field mydate doesnt really exists in the table when the WHERE clause is executed.
– Strae
                Sep 7, 2010 at 14:24
What about using str_to_date() to create a date from your format?
EDIT after reading your comment, I created a table like yours:
mysql> SELECT fid, fdate FROM test;
+------+------------+
| fid  | fdate      |
+------+------------+
|    1 | 10/9/2010  | 
|    2 | 17/9/2010  | 
|    3 | 19/09/2010 | 
+------+------------+
and then did 
mysql> SELECT fid FROM test WHERE STR_TO_DATE(fdate, '%d/%m/%Y') <= DATE_ADD(NOW(), INTERVAL 10 DAY);
+------+
| fid  |
+------+
|    1 | 
|    2 | 
+------+
Seems to work. What exactly do you get?
                Tryed, in the example it works with comma (07,09,2010) but not with slashes (07/09/2010) that's my case...i have then to first replace '/` with ,, then str_to_date
– Strae
                Sep 7, 2010 at 14:26
                Oooops youre right, i used to wrote the format wrong, as %m-%d-%Y instead of %m/%d/%Y! thanks guys
– Strae
                Sep 7, 2010 at 15:06
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.