将STR_TO_DATE格式化为日期

Hi I have been able to extract a VARCHAR to a date using string_to_date, however whatever I try the date always echoes as e.g. 2009-05-25
Here is my code that works:

您好我已经能够使用string_to_date将VARCHAR提取到日期,但是无论我尝试使用哪个日期,总是会回复到例如2009-05-25这是我的代码:

$query = "SELECT u.url_id, url, title, description, STR_TO_DATE( pub_date, '%d-%b-%Y') 
    AS pub_date FROM urls AS u, url_associations AS ua WHERE u.url_id = ua.url_id AND
    ua.url_category_id=$type AND ua.approved = 'Y' ORDER BY pub_date DESC";
$result = mysql_query ($query);
echo "  <tr>
<td align=\"left\">{$row['pub_date']}</td>
</tr>\n";

I have tried DATE_FORMAT and similar methods but I either receive 2009-05-25 or blank. Can anyone help me solve this issue. I am looking and testing but I have decided to ask for help here as the assistance is welcomed and those who helped previously were very kind.

我尝试了DATE_FORMAT和类似的方法,但我收到2009-05-25或空白。任何人都可以帮我解决这个问题。我正在寻找和测试,但我决定在这里寻求帮助,因为欢迎提供帮助,之前帮助过的人非常友善。

Thanks

Sean

2 个解决方案

#1

The return type of STR_TO_DATE is DATE which is returned to PHP.

STR_TO_DATE的返回类型是DATE,它返回给PHP。

It's PHP that formats dates in your echo, not MySQL.

这是PHP在回声中格式化日期,而不是MySQL。

To do the formatting on MySQL side, use:

要在MySQL端进行格式化,请使用:

DATE_FORMAT(STR_TO_DATE( pub_date, '%d-%b-%Y'), '%Y.%m.%d')

or other format.

或其他格式。

The inner format controls how your string is expected to be stored in the database, the outer format controls how will it be output.

内部格式控制着如何将字符串存储在数据库中,外部格式控制如何输出。

To do the formatting on PHP side (which is better as you can honor culture specific formats for different users), use:

要在PHP端进行格式化(这样可以更好地为不同用户提供特定于文化的格式),请使用:

echo date('Y m d', strtotime($row['pub_date']))

#2

From this reference site following are mysql command and output

从这个引用站点下面是mysql命令和输出

mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
        -> 'Sunday October 2009'
mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
        -> '22:23:00'
mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00',
    ->                 '%D %y %a %d %m %b %j');
        -> '4th 00 Thu 04 10 Oct 277'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
    ->                 '%H %k %I %r %T %S %w');
        -> '22 22 10 10:23:00 PM 22:23:00 00 6'
mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
        -> '1998 52'
mysql> SELECT DATE_FORMAT('2006-06-00', '%d');
        -> '00'

#1