Mysql Text to Date 不起作用 [英] Mysql Text to Date won't work

问题描述

我遇到了以下问题.我有一个类型为文本"的 mysql 字段，其中有很多生日日期.我想打印日期并按照我的日期格式对其进行排序.我无法更改数据库中的类型，因为它与某些用户配置文件值字段相关.

I got the following problem. I got a mysql field which is type "text" and there are many birthday dates in it. I want to print the dates out and sort it in my way of date format. I can't change the type in the database because it's related to some user profile value fields.

数据库中的值是这样的:

The value's thats in the database are such this:

1978-12-31 23:59:59

这是我的查询:

$result = mysql_query("SELECT a.value, b.username from yrt6h_community_fields_values a join yrt6h_users b on a.user_id = b.id where a.field_id = 3 order by a.value;");

这是我的示例 php 文件:

And this my example php file:

<table>
<tr>
<td>User:</td>
<td>Datum:</td>
</tr>
<?php
while($row = mysql_fetch_array($result)){
    echo '<tr>';
    echo '<td>';
    echo $row['username'] . ' : ';
    echo '</td>';
    echo '<td>';
    echo $row['value'];
    echo '</td></tr>';  
}
?>

</table>

我尝试了 mysql 的所有日期格式函数，但是我什么也没得到.例如我试过这个:

I tried all of the date format functions of mysql, but then i got nothing. For example I tried this:

mysql_query("SELECT str_to_date(a.value, '%d.%m.%Y'), ...

或

mysql_query("SELECT str_to_date(date_format(a.value, '%Y.%m.%d') %d.%m.%Y), ...

但我这样做了，日期的回声是空的.没有什么可打印的了.但为什么?即使我将此查询直接放入数据库中，我的生日值也为 NULL.

But I do that, the echo of the date is empty. Theres nothing to print out anymore. But why? Even if I put this query directly in the database I got NULL for the birthday value.

推荐答案

你为什么不试试用 php 来做，比如 -

why dont you try to do it with php like -

echo date('d-m-Y', strtotime($yourDate));//the first paramete is the date format you want and the second is the value you are getting from database.

参考以获得更多理解、示例和格式.

Reference for more understanding, example and formats.

这篇关于Mysql Text to Date 不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！