Rails scope using dates

Question

I'm trying to create a scope for invoice. It's to be used in an aging view.

The invoice date is invoice.invdate.

This didn't work - it thinks DateTime is a table:

 scope :thirtydays, where("DateTime.now - DATE(invdate) < ?", 31)

This is the pg error:

missing FROM-clause entry for table "datetime"

Chris Ledet · Accepted Answer · 2013-10-25 22:28:14Z

1

The problem is your passing Ruby code as a String inside a where clause instead of using String interpolation.

I see what you're trying to do but I think you should let Rails handle the dates lookups for you.

You can simplify your code with the following:

class Invoice < ActiveRecord::Base
  def self.thirtydays
    where(invdate: 31.days.ago..DateTime.now)
  end
end

31.days.ago..DateTime.now will generate a Range object starting with a date 31 days ago and ending with today's date. Rails will handle the SQL date range call for you depending on your database platform, which is Postgresql in this case.

It will generate a SQL statement similar to:

SELECT `invoices`.* FROM `invoices`  WHERE (`invoices`.`invdate` BETWEEN '2013-09-24 22:00:00' AND '2013-10-25 22:00:00')

edited Oct 25, 2013 at 22:28

answered Oct 25, 2013 at 21:18

Chris Ledet

11.6k7 gold badges43 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Rails scope using dates

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related