MYSQL Datetime as varchar 以及如何仅显示年份

Question

我有这个应用程序，其中 Musicalbums 的发行日期以 Varchar 形式保存到 MySQL 数据库中(我对此没有影响)，因此它显示的日期类似于 (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

试试这个...使用 mysql 的 Year 函数作为引用。 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