问题描述
p>我不能改变数据库结构(我构建一个小型插件),但是我必须查询数据库,查找这个数据字段在接下来的10天之间的行。示例:
fid | fdate |
| 1 | 10/09/2010 |
| 2 | 17/09/2010 |
| 3 | 19/09/2010 |
我必须使用fid 1和2获取行,因为日期是< = now + 10天。
通常我做这样的查询:
SELECT fid FROM table WHERE fdate< = DATE_ADD(NOW(),INTERVAL 10 DAY);
或者,如果日期格式为yyyymmdd:
SELECT fid FROM table WHERE fdate< = DATE_FORMAT(NOW(),'%Y%m%d');
但是,格式为$ code> dd / mm / yyyy
我已经弄清楚了
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) ;
但我想这是一个过度的重建日期格式与concat和子串,而有
子句不会帮助查询速度。
任何想法?
编辑
Adriano评论后,正确的解决方案是
SELECT fid FROM test WHERE STR_TO_DATE(fdate,'%d /%m /%Y')< = DATE_ADD(NOW(),INTERVAL 10 DAY);
所以我可以避免concat和having子句。
如何使用 然后执行 似乎要工作你究竟得到什么? Im working on a database that store dates in a 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: 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: Or, if the date is in the format `yyyymmdd': But theyre useless with the format I've figured out with but i guess it is a bit an overkill rebuilding the date format with concat and substring, and the Any idea? EDIT After Adriano's comment, the right solution is so i can avoid the concat and having clause. What about using str_to_date() to create a date from your format? EDIT after reading your comment, I created a table like yours: and then did Seems to work. What exactly do you get? 这篇关于Mysql:将'dd / mm / yyyy'转换为'yyyymmdd'的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!
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 |
+ ------ +
varchar(10)
mysql field (so sad).| fid | fdate |
| 1 | 10/09/2010 |
| 2 | 17/09/2010 |
| 3 | 19/09/2010 |
SELECT fid FROM table WHERE fdate <= DATE_ADD(NOW(), INTERVAL 10 DAY);
SELECT fid FROM table WHERE fdate <= DATE_FORMAT(NOW(), '%Y%m%d');
dd/mm/yyyy
.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);
having
clause wont help the query speed.SELECT fid FROM test WHERE STR_TO_DATE(fdate, '%d/%m/%Y') <= DATE_ADD(NOW(), INTERVAL 10 DAY);
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 |
+------+