CODEWITHSUNDEEP
ruby-on-railspostgresqlruby-on-rails-4
Kunal Vashist
Kunal Vashist

Reputation: 2471

Query to get result based on dates in rails?

Need to get result from query where start date and end date is in between the period and end date is nil

Tried this query

lines.where('((DATE(start_date) >= DATE(?) AND DATE(?) <= DATE(end_date)) or (DATE(start_date) >= DATE(?) AND DATE(end_date) is null))', start_date, end_date, start_date)

Database

Line 1 
start_date end_date
Oct 1, 2020 Oct 31, 2020
Line 2 
start_date end_date
01/01/2020   nil (MM/DD/YY)
Line 3 
start_date end_date
Oct 1, 2020 Jun 1, 2021
Line 4 
start_date end_date
Apr 1, 2021 Apr 30, 2021

Query where start_date and end_date is Oct 1, 2020 and Feb 1, 2021

result should be

Line 1 
start_date end_date
Oct 1, 2020 Oct 31, 2020
Line 2 
start_date end_date
01/01/2020   nil (MM/DD/YY)
Line 3 
start_date end_date
Oct 1, 2020 Jun 1, 2021

How can we do it?

Upvotes: 0

Views: 328

Answers (1)

Sampat Badhe
Sampat Badhe

Reputation: 9075

lines.where('((DATE(start_date) >= DATE(?) AND DATE(end_date) <= DATE(?)) or (DATE(start_date) >= DATE(?) AND DATE(end_date) is null))', start_date, end_date, start_date)

same can be written as

lines.where('((DATE(start_date) >= DATE(:start_date) AND DATE(end_date) <= DATE(:end_date)) OR (DATE(start_date) <= DATE(:start_date) AND DATE(end_date) is null))', start_date: start_date, end_date: end_date)

Upvotes: 0

Related Questions