mySQL - querying datetime field based on year

[TheMenace]

I'm wondering if there's a simple way to query a mySQL 4 table in order to get all records whose datetime field (say 'dateposted') is equal to a known year?

e.g:
select * from table where year(dateposted)=myYearVariable

 

[RedCardinal]

There is indeed.

SELECT * FROM table WHERE DATE_FORMAT(dateposted, '%Y') = myYearVariable

I think something like that will work, but would need to actually try it to make sure. It assumes that the dateposted field is actually a date type.

Drop me a mail if ou're stuck

 

[TheMenace]

Thank... but nope, that doesn't seem to work. It's actually a datetime data type.

 

[daviddoran]

I always use INT for date/time fields, much easier to work with and you can do more with them.

 

[RedCardinal]

What did it say? It might need something like:

SELECT DATE_FORMAT(dateposted, '%Y') AS year, * FROM table WHERE year=myYearVariable

I haven't had the *pleasure* of SQL db's for a little while...

 

[TheMenace]

Actually the first one did work. Um, embarrassingly enough I was missing a space from my SQL string that was screwing things up.

Thanks Richard! Just splendid!