Rails 3 & ActiveRecord - sql query about date

Question

I build my simple blog system and every article in the system has two inputs for setting the the, from that the article will be visible for the visitors and the second input is for the date, till that will be visited.

For example, if I will set to this inputs the values

2011-10-25 and 2011-10-28, so that's mean, the article will be visible 3 days. If I wanted to have an article still visible, so I would like to have in inputs the values 0000-00-00.

But now I think how to make an sql query - I try something like:

Article.where('from_date <= ? AND to_date >= ?', Func.today, Func.today)

This works me if I have set a date, from that and till that I want to have an article visible. But if I will set that I want to have an article visible everytime (0000-00-00), so this query doesn't works...

I would like to ask you about the hint, how to update the sql query above for it...

Thanks in advance,

Manny

Douglas F Shearer · Accepted Answer · 2011-10-25 00:21:39Z

2

Instead of setting the date fields to all zeros, set them to nil and look for null values in the query.

Article.where('(from_date IS NULL OR from_date <= ?) AND (to_date IS NULL OR to_date >= ?)', Func.today, Func.today)

answered Oct 25, 2011 at 0:21

Douglas F Shearer

26.6k2 gold badges51 silver badges48 bronze badges

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

2 Comments

user984621 Over a year ago

Thanks for your reply Douglas. This is my generated query:

SELECT articles.* FROM articles WHERE ((from_date IS NULL OR from_date <= '2011-10-25') AND (to_date IS NULL OR to_date >= '2011-10-25'))

- I set I want to show an article from 28th October (in the database is the value 2011-10-28) - but the article is still showed (already now)

clyc Over a year ago

@user984621 set the from_date to 2011-10-28 and the to_date to null

Collectives™ on Stack Overflow

Rails 3 & ActiveRecord - sql query about date

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related