Rails Sql Query for MONTH AND YEAR

I'm trying to show sales transactions for the current month and year in my index view.

This is what I've put in my sales controll开发者_如何学Pythoner:

def index
 @sales = show_sales_for_current_month(@sales)

which uses this method in the SalesHelper

def show_sales_for_current_month(sales)
  sales = Sale.find(:all,
                  :conditions => ["MONTH(date) = ? AND YEAR(date) =?",
                                  Date.today.month, Date.today.year])
end

where date is a date data type.

but i'm getting the following controller error:

SQLite3::SQLException: no such function: MONTH: SELECT "sales".* FROM "sales" WHERE (MONTH(date) = 4 AND YEAR(date) =2011)

I've looked around at posts and it seems like that is the correct function, so what am I doing wrong? Thanks!

Hey there, I have a gem called by_star which could help you with these kinds of queries. In your case you would only need to do this in your controller:

@sales = Sale.by_month

by_star takes care of working out what month and year it is, as well as the messy SQL.

The functions MONTH and YEAR do not exist in SQLite3. You can take an approach like this (taken from my current project):

model entry.rb:

  def self.all_entries_year(year, user_id)
    where('entries.user_id = :id', :id => user_id ).
    where(':first_day <= entries.date AND entries.date <= :last_day', { 
        :first_day => Date.new(year, 1, 1),
        :last_day => Date.new(year, 12, 31)
    }).
    order('date desc')
  end

EDIT:

Put this in your model: sales.rb (I assume, it has the field date)

def self.show_sales_for_current_month(year, month)

  mydate = Date.new(year, month, 1)

  where(':first_day <= sales.date AND sales.date <= :last_day', { 
        :first_day => mydate,
        :last_day => mydate.at_end_of_month
  }).
  order('date')
end

In MySQL this works, but you are using SQLite3 so you need to modify your query to use the SQLite3 version of MONTH and YEAR, which means using the strftime function:

sales = Sale.find(:all,
  :conditions => ["strftime('%m', date) = '?' AND strftime('%Y', date) = '?'",
    '%02d' % Date.today.month, Date.today.year])