Rails ActiveRecord - how to fetch records between two dates

Question

I have two date columns - from_date and to_date in a database table.

Example:

• from_date: 2012-09-10
• to_date: 2012-09-30
• today: 2012-09-13

I need to fetch all records, if today's date is between from_date and to_date. How do I do that with a SQL query?

If I have loaded the respective record, I can easily decide, if today's date is between from_date and to_date, but I don't know how to fetch those records straight from the database table.

Solution 1

data = ModelName.where("today >= from_date AND today <= to_date")

Solution 2

Check the Rails guides on Range conditions:

Client.where(created_at: (Time.now.midnight - 1.day)..Time.now.midnight)

That will produce the following SQL:

SELECT * FROM clients WHERE (clients.created_at BETWEEN '2008-12-21 00:00:00' AND '2008-12-22 00:00:00')

Solution 3

A secure and easy way to do this would be:

Model.where(':date BETWEEN from_date AND to_date', date: Date.current)

Solution 4

you can use like this:

Data = Model.where("date(now()) between from_date and to_date")

Solution 5

data = ModelName.find(:all, :conditions => "today >= from_date and today <= to_date")

View more solutions