如何获取具有给定日期值的varchar date列

n7taea2i 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(300)

我有一张table。其中，列名为tb\u date，为varchar格式。在该列中，日期是保存的，但格式不同（如：01/07/201或01-08-2018或2017/03/12等）。现在我要在给定日期之间搜索。但它不起作用。我试过了-

$result=mysql_query("select * from user_History") or die(mysql_error());
while($row=mysql_fetch_array($result))
{
extract($row);
$time = strtotime( $tb_date );
$myDate = date( 'd/m/y', $time );

$result1=mysql_query("select * from user_History where '$myDate' BETWEEN '01/06/2018' AND '31/06/2018'") or die(mysql_error()); 
while($row1=mysql_fetch_array($result1))
{
    extract($row1);

    echo $tb_date;
    }                   
    }

但它提供了所有的记录。

sql mysql php ajax mysqli

来源：https://stackoverflow.com/questions/51151136/how-to-fetch-varchar-date-column-with-given-date-values

2条答案

按热度按时间

jvlzgdj91#

您可以使用这个if语句来转换这个日期格式，但这不是最好的主意。更好的方法是规范化此字段：

IF( tb_date REGEXP '^[0-9]{4}/[0-9]{2}/[0-9]{2}$', str_to_date(tb_date,'%Y/%m/%d'),
IF( tb_date REGEXP '^[0-9]{2}/[0-9]{2}/[0-9]{4}$', str_to_date(tb_date,'%d/%m/%Y'),
IF( tb_date REGEXP '^[0-9]{2}-[0-9]{2}-[0-9]{4}$', str_to_date(tb_date,'%d-%m-%Y') , 'ERROR'))) tb_date_new

样品

MariaDB [bernd]> SELECT * from user_History;
+----+------------+------------+
| id | tb_date    | some-field |
+----+------------+------------+
|  1 | 01/07/2014 | Line 1     |
|  2 | 01-08-2018 | Line 2     |
|  3 | 2017/03/12 | Line 3     |
+----+------------+------------+
3 rows in set (0.00 sec)

MariaDB [bernd]> SELECT u.*,
    -> IF( tb_date REGEXP '^[0-9]{4}/[0-9]{2}/[0-9]{2}$', str_to_date(tb_date,'%Y/%m/%d'),
    -> IF( tb_date REGEXP '^[0-9]{2}/[0-9]{2}/[0-9]{4}$', str_to_date(tb_date,'%d/%m/%Y'),
    -> IF( tb_date REGEXP '^[0-9]{2}-[0-9]{2}-[0-9]{4}$', str_to_date(tb_date,'%d-%m-%Y') , 'ERROR'))) tb_date_new
    -> FROM user_History u
    -> ;
+----+------------+------------+-------------+
| id | tb_date    | some-field | tb_date_new |
+----+------------+------------+-------------+
|  1 | 01/07/2014 | Line 1     | 2014-07-01  |
|  2 | 01-08-2018 | Line 2     | 2018-08-01  |
|  3 | 2017/03/12 | Line 3     | 2017-03-12  |
+----+------------+------------+-------------+
3 rows in set (0.00 sec)

MariaDB [bernd]> SELECT u.*,
    -> IF( tb_date REGEXP '^[0-9]{4}/[0-9]{2}/[0-9]{2}$', str_to_date(tb_date,'%Y/%m/%d'),
    -> IF( tb_date REGEXP '^[0-9]{2}/[0-9]{2}/[0-9]{4}$', str_to_date(tb_date,'%d/%m/%Y'),
    -> IF( tb_date REGEXP '^[0-9]{2}-[0-9]{2}-[0-9]{4}$', str_to_date(tb_date,'%d-%m-%Y') , 'ERROR'))) tb_date_new
    -> FROM user_History u
    ->
    -> WHERE
    -> IF( tb_date REGEXP '^[0-9]{4}/[0-9]{2}/[0-9]{2}$', str_to_date(tb_date,'%Y/%m/%d'),
    -> IF( tb_date REGEXP '^[0-9]{2}/[0-9]{2}/[0-9]{4}$', str_to_date(tb_date,'%d/%m/%Y'),
    -> IF( tb_date REGEXP '^[0-9]{2}-[0-9]{2}-[0-9]{4}$', str_to_date(tb_date,'%d-%m-%Y') , 'ERROR')))
    -> BETWEEN str_to_date('01/06/2018','%d/%m/%Y') AND str_to_date('31/08/2018','%d/%m/%Y');
+----+------------+------------+-------------+
| id | tb_date    | some-field | tb_date_new |
+----+------------+------------+-------------+
|  2 | 01-08-2018 | Line 2     | 2018-08-01  |
+----+------------+------------+-------------+
1 row in set (0.00 sec)

MariaDB [bernd]>

赞(0）回复(0）举报 2021-06-20

svdrlsy42#

假设您在mysql中的日期值类型为date，那么您应该在sql语句中以yyyy-mm-dd的格式给出它。
改变

select * from user_History where '$myDate' BETWEEN '01/06/2018' AND '31/06/2018'

到

select * from user_History where `[DATE_COLUMN_NAME]` BETWEEN '2018-06-01' AND '2018-06-31'

希望这有帮助。

赞(0）回复(0）举报 2021-06-20

我来回答

如何获取具有给定日期值的varchar date列

2条答案

相关问题

热门标签

最新问答