Mysql Text to Date 不起作用

本文介绍了Mysql Text to Date 不起作用的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我遇到了以下问题.我有一个类型为文本"的 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.