mysql match a year withing datetime field

Question

I am wondering if this is possible, in my database table, I have a datetime field, I wanting to query that table and pull in all the rows that have a posted date of 2011, however my date posted column is dateTime, so the form looks like this,

2011-06-12 00:00:00

How can I query the table to return all 2011 rows?

Answer 1

SELECT * FROM yourtable WHERE YEAR(datetime_column) = '2011' 

Read more about YEAR()

Answer 2

It should work with datetime type too.

SELECT * FROM tblName WHERE YEAR(column) = '2011' 

Answer 3

Try with:

SELECT *
  FROM your_table
 WHERE EXTRACT(YEAR FROM datetime_column) = '2011'

Answer 4

You can even sub-string it if you want...

SELECT * FROM tblName WHERE substr(column,1,4) = '2011' 

I would however recommend creating a "date dimension" table, and then splitting your dateTime field into separate date and time fields, then joining to the "date dimension" table via the date field, which allows for much better functionality. See the answer I put on the following post for more information about using a "date dimension" table.

Select all months within given date span, including the ones with 0 values

You can join your table to it as follows;

SELECT tblName.*
FROM tblName
INNER JOIN dimDates AS dd
    ON tblName.dateField = dd.dd_date
    AND dd.dd_date = "2011"