Rails Sql Request for MONTH AND YEAR

Question

I am trying to show sales transactions for the current month and year in my index view.
This is what I added to the sales controller:

def index @sales = show_sales_for_current_month(@sales) 

which uses this method in 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 the date data type.

but I get the following controller error:

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

I looked through the posts and it looks like this is the right function, so what am I doing wrong? Thanks!

Answer 1

In MySQL, this works, but you are using SQLite3, so you need to change your query to use the SQLite3 MONTH and YEAR versions, 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]) 

Answer 2

Hey, I have a gem called by_star that can help you with these types of queries. In your case, you will only need to do this in your controller:

 @sales = Sale.by_month 

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

Answer 3

MONTH and YEAR functions do not exist in SQLite3. You can do this approach (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 (suppose it has a 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