MySQL从日期格式中提取年份 [英] mysql extract year from date format

问题描述

我需要一个mysql查询来从数据库表中的以下日期格式中提取年份.

I need a mysql query to extract the year from the following date format from a table in my database.

例如:

        subdateshow
      ----------------      
        01/17/2009
        01/17/2009
        01/17/2009
        01/17/2009
        01/17/2009

以下查询无效

select YEAR ( subdateshow ) from table 

列类型为varchar.有什么办法解决这个问题?

The column type is varchar. Is there any way to solve this?

推荐答案

由于您的subdateshow是VARCHAR列，而不是正确的DATE、TIMESTAMP或DATETIME列，您必须先将字符串转换为日期，然后才能在其上使用YEAR:

Since your subdateshow is a VARCHAR column instead of the proper DATE, TIMESTAMP or DATETIME column you have to convert the string to date before you can use YEAR on it:

SELECT YEAR(STR_TO_DATE(subdateshow, "%m/%d/%Y")) from table

请参见 http://dev. mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date

这篇关于MySQL从日期格式中提取年份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！