MySQL mysql从日期格式中提取年份
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8712771/
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
mysql extract year from date format
提问by Renjith R
I need a mysql query to extract the year from the following date format from a table in my database.
我需要一个 mysql 查询来从我的数据库表中的以下日期格式中提取年份。
For eg :
例如：
subdateshow
----------------
01/17/2009
01/17/2009
01/17/2009
01/17/2009
01/17/2009
the following query didn't working
以下查询无效
select YEAR ( subdateshow ) from table
The column type is varchar. Is there any way to solve this?
列类型为 varchar。有没有办法解决这个问题？
回答by Gordon
Since your subdateshow is a VARCHAR column instead of the proper DATE, TIMESTAMP or DATETIME columnyou have to convert the string to date before you can use YEAR on it:
由于您的 subdateshow 是一个 VARCHAR 列，而不是正确的DATE、TIMESTAMP 或 DATETIME 列，因此您必须先将字符串转换为日期，然后才能在其上使用 YEAR：
SELECT YEAR(STR_TO_DATE(subdateshow, "%m/%d/%Y")) from table
See http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date
见http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date
回答by NooNa MarJa
SELECT EXTRACT(YEAR FROM subdateshow) FROM tbl_name;
回答by Jamshid Hashimi
You can try this:
你可以试试这个：
SELECT EXTRACT(YEAR FROM field) FROM table WHERE id=1
回答by pgl
This should work:
这应该有效：
SELECT YEAR(STR_TO_DATE(subdateshow, '%m/%d/%Y')) FROM table;
eg:
例如：
SELECT YEAR(STR_TO_DATE('01/17/2009', '%m/%d/%Y')); /* shows "2009" */
回答by ram4nd
try this code:
试试这个代码：
SELECT DATE_FORMAT(FROM_UNIXTIME(field), '%Y') FROM table
回答by emcomments
This should work if the date format is consistent:
如果日期格式一致，这应该有效：
select SUBSTRING_INDEX( subdateshow,"/",-1 ) from table
回答by Sudhir Bastakoti
TRY:
尝试：
SELECT EXTRACT(YEAR FROM (STR_TO_DATE(subdateshow, '%d/%m/%Y')));
回答by Boopathi
try this code:
试试这个代码：
SELECT YEAR( str_to_date( subdateshow, '%m/%d/%Y' ) ) AS Mydate
SELECT YEAR( str_to_date( subdateshow, '%m/%d/%Y' ) ) AS Mydate
