CODEWITHSUNDEEP
sqlruby-on-rails-3
Mike
Mike

Reputation: 1684

Rails where date is greater than given date query

Trying to search where movies coming out have a release date greater than today's date

 Movie.where('release > ?', Date.today)
ActiveRecord::StatementInvalid: Mysql::ParseError: You have an error in your SQL     syntax;    check the manual that corresponds to your MySQL server version for the right syntax to use near 'release > '2011-09-25')' at line 1: SELECT `movies`.* FROM `movies` WHERE (release > '2011-09-25')

Upvotes: 90

Views: 102177

Answers (6)

Mark Swardstrom
Mark Swardstrom

Reputation: 18120

  Movie.where(release: Date.tomorrow..)

Upvotes: 0

rinold simon
rinold simon

Reputation: 3052

Ruby beginless/endless ranges can also be used as an out-of-the-box solution:

Post.where(id: 1..)

=> Post Load (0.4ms) SELECT "posts".* FROM "posts" WHERE "posts"."id" >= $1 [["id", 1]]

Post.where(id: ..9)

=> Post Load (0.3ms) SELECT "posts".* FROM "posts" WHERE "posts"."id" <= $1 [["id", 9]]

Post.where(id: ...9)

=> Post Load (0.3ms) SELECT "posts".* FROM "posts" WHERE "posts"."id" < $1 [["id", 9]]

Note:

Replace id with your date column release

Replace value with Date.today

Upvotes: 5

Jos&#233; Mateus
Jos&#233; Mateus

Reputation: 331

In Ruby 2.7, you can try this:

License.where(expiration: Time.zone.today..)

SELECT "licenses".* FROM "licenses" WHERE "licenses"."expiration" >= '2021-07-06 15:12:05'

Upvotes: 17

Marian13
Marian13

Reputation: 9328

Update

Rails core team decided to revert this change for a while, in order to discuss it in more detail. See this comment and this PR for more info.

I am leaving my answer only for educational purposes.

new 'syntax' for comparison in Rails 6.1 (Reverted)

Movie.where('release >': DateTime.now)

Here is a link to PR where you can find more examples.

Upvotes: 18

Patrick
Patrick

Reputation: 1387

In recent versions of rails, you can do this:

User.where(created_at: 3.days.ago..Time.now)

See some other examples here: https://stackoverflow.com/a/24150094

Upvotes: 45

Adam Eberlin
Adam Eberlin

Reputation: 14205

Rails 3+ :

Movie.where('release > ?', DateTime.now)

Pre Rails 3

Movie.where(['release > ?', DateTime.now])

Upvotes: 148

Related Questions