Efficient way to implement this ActiveRecord Query

Question

Hi

I am using MySQL database and I have a datetime field inside it. In my rails application I have to fire a Query similar to the following,

MyTable.all(:conditions=>{my_date.to_date=>"2010-07-14"})

The my_date field is of datatype datetime. I should omit the time and should directly compare it with a date (but I am encountering an error near my_date.to_date due to obvious reasons). How to write an ActiveRecord Query for this scenario?

Thanks.

Answer 1

MyTable.all(:conditions=>["DATE_FORMAT(my_date, '%Y-%d-%m')=?", "2010-07-14"])

EDITED i think it should be

MyTable.all(:conditions=>["DATE_FORMAT(my_date, '%Y-%m-%d')=?", "2010-07-14"])

Answer 2

Here is an efficient solution if you index the date column:

d = "2010-07-14".to_date
MyTable.all(:conditions=>["my_date BETWEEN ? AND ?", 
           d.beginning_of_day, d.end_of_day)