Query for all records with datetime in 2005? 1

[Yrrk]

I have a table with a datetime field. I'd like to query all records for 2005 based on that field. I'm having trouble coming up with a where clause that does this.

I'm on MySQL 4.1

 

[r937]

where datetimecolumn >= '2005-01-01'
and datetimecolumn < '2006-01-01'

this is by far the simplest way, and it happens also to be the one which is the most efficient if there is an index on the column

r937.com | rudy.ca

 

[r937]

sorry, "by far the simplest" is perhaps a bit overstated

this is probably the simplest --

where year(datetimecolumn) = 2005

however, this will require a table scan

r937.com | rudy.ca

 

[towerbase]

I find that something like

WHERE datetimecolumn BETWEEN '2005-01-01' AND '2006-01-01'

is the easiest to read (and modify)

Andrew
Hampshire, UK

 

[r937]

andrew, that's about 99.99% correct

it will (incorrectly) include midnight on 2006-01-01

that's why i used >= and <

r937.com | rudy.ca

 

[towerbase]

Thanks for pointing that out, Rudy.


Andrew
Hampshire, UK