在 MySQL 中格式化日期以返回 MonthName、Year
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8161227/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Format date in MySQL to return MonthName, Year
提问by Andrew
I have rows in a table that I need to order by date. Easy enough, use the datedatatype to store them, ORDER BYdate blah blah.
我需要按日期订购表格中的行。很简单，使用date数据类型来存储它们，ORDER BY日期等等。
However, I'm trying to get these rows, and easily convert the date to MonthName, Year
但是，我正在尝试获取这些行，并轻松地将日期转换为 MonthName, Year
If I had stored a row's date as 2011-11-16, I would want to extract this (using PHP) and print out:
如果我将一行的日期存储为2011-11-16，我想提取它（使用 PHP）并打印出来：
November, 2011
回答by Nalaka526
Try this
尝试这个
SELECT DATE_FORMAT(`date`,'%M %Y') AS showdate FROM table
Use this site http://www.mysqlformatdate.com/to get the (mysql) date format you want
使用此站点http://www.mysqlformatdate.com/获取您想要的 (mysql) 日期格式
回答by Erwin Brandstetter
SELECT DATE_FORMAT(date_col, '%M, %Y')
FROM tbl
-- WHERE ??
ORDER BY date_col;
The manual knows moreabout that.
回答by Ganesh Giri
You can get date year and monthName formate using the following query.
您可以使用以下查询获取日期 year 和 monthName 格式。
SELECT DATE_FORMAT("2017-06-15", "%Y %M") as 'Date';
If you need other formats apply following combinations.
如果您需要其他格式，请应用以下组合。
Syntax: DATE_FORMAT(date, format_mask)
句法： DATE_FORMAT(date, format_mask)
Format Description
%a Abbreviated weekday name (Sun to Sat)
%b Abbreviated month name (Jan to Dec)
%c Numeric month name (0 to 12)
%D Day of the month as a numeric value, followed by suffix (1st, 2nd, 3rd, ...)
%d Day of the month as a numeric value (01 to 31)
%e Day of the month as a numeric value (0 to 31)
%f Microseconds (000000 to 999999)
%H Hour (00 to 23)
%h Hour (00 to 12)
%I Hour (00 to 12)
%i Minutes (00 to 59)
%j Day of the year (001 to 366)
%k Hour (0 to 23)
%l Hour (1 to 12)
%M Month name in full (January to December)
%m Month name as a numeric value (00 to 12)
%p AM or PM
%r Time in 12 hour AM or PM format (hh:mm:ss AM/PM)
%S Seconds (00 to 59)
%s Seconds (00 to 59)
%T Time in 24 hour format (hh:mm:ss)
%U Week where Sunday is the first day of the week (00 to 53)
%u Week where Monday is the first day of the week (00 to 53)
%V Week where Sunday is the first day of the week (01 to 53). Used with %X
%v Week where Monday is the first day of the week (01 to 53). Used with %X
%W Weekday name in full (Sunday to Saturday)
%w Day of the week where Sunday=0 and Saturday=6
%X Year for the week where Sunday is the first day of the week. Used with %V
%x Year for the week where Monday is the first day of the week. Used with %V
%Y Year as a numeric, 4-digit value
%y Year as a numeric, 2-digit value
回答by devpro
Try this in php:
在 php 中试试这个：
<?=format_date($column_name['date'],"M, Y")?>
回答by okconfused
Once you get date in mysql, use this in php
在 mysql 中获得日期后，在 php 中使用它
$date = mysql_result(mysql_query("SELECT date FROM table"),0,0);
echo date("F, Y", strtotime($date));
回答by Onwu Bishop Gideon
try this
尝试这个
$sql = "SELECT DATE_FORMAT(`date`,\'%M %Y\') AS showdate FROM `allowances`";
回答by Eduardo Iglesias
In SQL you could get
在 SQL 中你可以得到
SELECT year(DBDate), month(DBDate) FROM dates ORDER BY DBDate
