mysql - CAST(日期 AS DATE) 日期格式

Question

我有以下 php 例程来从表中提取值。我正在尝试将 DATE 类型从“2014-08-10”值转换为“20140810”值

返回时间:

//query
$query = mysql_query("SELECT CAST(date AS DATE), EtotalDay from MonthData group by 1 order by 1") 
OR die ('Query is invalid: ' . mysql_error());

//write the results
while ($row = mysql_fetch_array($query)) {echo  $row['CAST(date AS DATE)'] . "," . $row['EtotalDay'] . "\n";

我怎样才能做到这一点？

Answer 1

你应该使用

DATE_FORMAT(date, '%Y%m%d')

代替 CAST()，使用列别名来完成字段检索

$query = mysql_query("SELECT DATE_FORMAT(date, '%Y%m%d') formattedDate, EtotalDay from MonthData group by 1 order by 1") or die ('Query is invalid: ' . mysql_error());


//write the results
while ($row = mysql_fetch_array($query)) {echo  $row['formattedDate'] . "," . $row['EtotalDay'] . "\n";

参见手册，DATE_FORMAT

DATE_FORMAT(date,format)

Formats the date value according to the format string.

The following specifiers may be used in the format string. The “%” character is required before format specifier characters.

%d Day of the month, numeric (00..31)
%m Month, numeric (00..12)
%Y Year, numeric, four digits