Alex Rivera | Logout

Get the year out of timestamp sql

Asked 2010-07-05T14:03:34.263
19

I have a problem extracting the year out of a mysql timestamp field. I managed to get it work with a datetime field using this method:

SELECT id FROM TABLE WHERE YEAR(creation_date) = 2010

CREATE TABLE IF NOT EXISTS `pub_media` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  `title` text,
  `filename` text,
  `path` text,
  `serv_path` text,
  `type` enum('images','videos','files','audio','gallery') DEFAULT NULL,
  `keywords` text,
  `label_id` int(11) DEFAULT NULL,
  `creation_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `update_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  `rank` int(11) NOT NULL DEFAULT '0',
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Server version : 5.1.41 Is there a simalar way to perform this action on a timestamp field? I want to keep this action in my SQL statement and not try to move it to PHP or any other scripting language, if possible.

Edit
Report

1 Answer

8

use CAST function :)

SELECT id FROM TABLE WHERE YEAR(CAST(creation_date AS DATE))=2010

should work with creation_date timestamp

[[edit]] ,added () around cast

answered 2010-07-05T14:10:25.103

Your Answer