MYSQL日期时间为varchar，如何只显示年份

Question

我有这个应用程序，其中音乐专辑的Releasedates被保存到MySQL数据库作为Varchar(我对此没有影响)，所以它显示的日期如(2014-09-11)。我怎样才能使发布日期只显示为(2014) ?需要转换成DATETIME

吗?

我的MySQl查询是这样的:

    SELECT products.pro_id, 
           products.title AS album_title, 
           products.genre AS album_genre, 
           products.coverarttoenailurl AS cover_image, 
           products.ccws_pro_id AS product_upc, 
           track_title, 
           track_duration,  
           album_id, 
           physicalReleaseDate, 
           digitalReleaseDate 
   FROM products 
   INNER JOIN track_albumn_information 
   ON products.ccws_pro_upc = track_albumn_information.product_upc 
   AND track_albumn_information.artist_id ='".$artist_id."' 
   ORDER BY physicalReleaseDate, digitalReleaseDate DESC

相关字段为physicalReleaseDate和digitalReleaseDate

Answer 1

Try This…使用mysql的Year function for ref. http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_year

SELECT products.pro_id,products.title AS album_title, products.genre AS album_genre, products.coverarttoenailurl AS cover_image, products.ccws_pro_id 
                AS product_upc, track_title, track_duration, album_id, Year(physicalReleaseDate) , year(digitalReleaseDate) FROM products INNER JOIN track_albumn_information 
                ON products.ccws_pro_upc = track_albumn_information.product_upc AND track_albumn_information.artist_id ='".$artist_id."' ORDER BY physicalReleaseDate, digitalReleaseDate DESC

Answer 2

因为它是varchar列，所以使用use LEFT函数

LEFT(physicalReleaseDate,4)

Answer 3

您可以使用LEFT()，它从字符串的左侧返回指定数量的字符，即

LEFT(digitalReleaseDate,4)

。

Answer 4

YEAR(STR_TO_DATE(datecolumn,'%Y-%m-%d'))

Answer 5

SUBSTR(physicalReleaseDate, 1, 4)
SUBSTR(digitalReleaseDate, 1, 4)