mySQL - querying datetime field based on year

**TheMenace** · 09-02-2007, 07:55 PM

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** · 09-02-2007, 09:10 PM

There is indeed.

Code:

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** · 09-02-2007, 09:51 PM

Thank... but nope, that doesn't seem to work.

It's actually a datetime data type.

**daviddoran** · 09-02-2007, 10:00 PM

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

**RedCardinal** · 09-02-2007, 10:12 PM

What did it say? It might need something like:

Code:

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** · 09-02-2007, 10:34 PM

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!

Thread: mySQL - querying datetime field based on year

LinkBack

Thread Tools

Display

mySQL - querying datetime field based on year

Visitors found this page by searching for:

mysql where year equals

MySQL DateTime Year

mysql query datetime field

Tags for this Thread

Posting Permissions