Geek Answers Handbook

Mysql: convert date from 'dd / mm / yyyy' to 'yyyymmdd'

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

I can’t change the database structure (I’m creating a small plugin), but I need to query the database where the rows are located, 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 need to get lines with fid 1 and 2 because the date is <= now + 10 days.

I usually make this request:

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 understood 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 think this will outwit a bit by rebuilding the date format with concat and substring, and the sentence havingwill not support query speed.

Any idea?

EDIT

SELECT fid FROM test WHERE STR_TO_DATE(fdate, '%d/%m/%Y') <= DATE_ADD(NOW(), INTERVAL 10 DAY);

concat having.

+5
1

str_to_date() ?

EDIT , , :

mysql> SELECT fid, fdate FROM test;
+------+------------+
| fid  | fdate      |
+------+------------+
|    1 | 10/9/2010  | 
|    2 | 17/9/2010  | 
|    3 | 19/09/2010 | 
+------+------------+


mysql> SELECT fid FROM test WHERE STR_TO_DATE(fdate, '%d/%m/%Y') <= DATE_ADD(NOW(), INTERVAL 10 DAY);
+------+
| fid  |
+------+
|    1 | 
|    2 | 
+------+

, . ?

+7

More articles:


All Articles