CODEWITHSUNDEEP
mysql
Udders
Udders

Reputation: 6976

mysql match a year withing datetime field

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?

Upvotes: 10

Views: 14580

Answers (4)

Dave Rix
Dave Rix

Reputation: 1689

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"

Upvotes: 1

Ghazanfar Mir
Ghazanfar Mir

Reputation: 3541

It should work with datetime type too.

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

Upvotes: 4

hsz
hsz

Reputation: 152246

Try with:

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

Upvotes: 3

Jason McCreary
Jason McCreary

Reputation: 73001

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

Read more about YEAR()

Upvotes: 24

Related Questions