Mysql：将'dd / mm / yyyy'转换为'yyyymmdd' [英] Mysql: converting date fro 'dd/mm/yyyy' to 'yyyymmdd'

问题描述

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子句。

解决方案

如何使用

  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 | 
 + ------ + 
  

似乎要工作你究竟得到什么？

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?

EDIT

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.

解决方案

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?

这篇关于Mysql：将'dd / mm / yyyy'转换为'yyyymmdd'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！