rails: select record if current date is between two date and show in view

Question

In my database I have two columns named start_date and end_date, and I want to get the entry from form my database if today date is between start_date and end_date. My current query code is as follows but it's not returning what I expect:

def updateMess
  @mess = MessType.find(params[:id_param])
  @today = Date.today
  @mess_current_price = MessPrice.where('mess_id = ? AND DATE(?) BETWEEN start_date AND end_date', @mess.mess_id, @today)

  respond_to do |format|
    format.json { render :json => { :_mess => @mess, :_mess_price => @mess_current_price } }
  end
end

The above where command generates the following query (as seen in console):

MessPrice Load (0.4ms) SELECT mess_prices.* FROM mess_prices WHERE (DATE('2013-07-01') BETWEEN start_date AND end_date)

Completed 200 OK in 17ms (Views: 3.1ms | ActiveRecord: 2.7ms)

EDIT: Following is the code for my javascript which I used to set the fields in my view by using the parsed JSON data, but it is not setting the fields in my view:

$(document).on('click', ".mess-update-reveal", function(){
  $.post('/mess/updateMess',{id_param :$(this).attr('data-mess-id')}, function(data){
    var mess = data._mess;
    var messPrice = data._mess_price;
    console.log(data);
    $('#id_param_update_mess').val(mess.mess_id);
    $('#mess_name_update').val(mess.mess_name);
    $('#id_mess_charge_update').val(messPrice.id);
    $('#mess_breakfast_charge_update').val(messPrice.breakfast_charge);
    $('#mess_lunch_charge_update').val(messPrice.lunch_charge);
    $('#mess_dinner_charge_update').val(messPrice.dinner_charge);
    $('#mess_monthly_charge_update').val(messPrice.monthly_charge);
    $('#mess_semesterly_charge_update').val(messPrice.semesterly_charge);
    $('#mess_capacity_update').val(mess.mess_capacity);
    $('#start_date_update').val(messPrice.start_date);
    $('#end_date_update').val(messPrice.end_date);
    $('#rebate_update').val(messPrice.rebate);
  });
});

The result shown by console.log(data) is as follows:

Object
_mess: Object
created_at: "2013-06-26T07:16:58Z"
hostel_id: null
mess_capacity: 200
mess_id: 1
mess_name: "Mess1"
updated_at: "2013-06-29T12:08:08Z"
__proto__: Object
_mess_price: Array[1]
0: Object
breakfast_charge: 30
created_at: null
dinner_charge: 45
end_date: "2013-12-31"
id: 1
lunch_charge: 45
mess_id: 1
monthly_charge: 2000
rebate: 100
semesterly_charge: 11000
start_date: "2013-06-01"
updated_at: "2013-06-29T12:38:51Z"
__proto__: Object
length: 1
__proto__: Array[0]
__proto__: Object

Did you try trigering the query directly? Just to check if the data format is as expected — AnkitG, Jul 01 '13 at 06:34
@Mischa where is generating the correct code, their is no error but the code is not retrieving the result their is one entry in database with start_date = '2013-06-01' and end_date = '2013-12-31'. So it should return one result. — hiteshtr, Jul 01 '13 at 10:45
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/32676/discussion-between-hiteshtr-and-mischa) — hiteshtr, Jul 01 '13 at 11:22

score 5 · Accepted Answer · answered Jul 02 '13 at 04:08

data._mess_price is an array, so you either have to return the first element in the array:

var messPrice = data._mess_price[0];

or make sure that data._mess_price is not an array by using first at the end of your ActiveRecord call:

@mess_current_price = MessPrice.where('mess_id = ? AND DATE(?) BETWEEN start_date AND end_date', @mess.mess_id, @today).first

There is nothing wrong with the way you're using BETWEEN. The query works fine.

score 0 · Answer 2 · edited May 23 '17 at 12:23

0

Ref this or this

MessPrice.where('start_date < ? AND end_date > ?', @today,  @today)

Syntax for between clause is something like following

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

where as you are using it like following

SELECT column_name(s)
FROM table_name
WHERE value BETWEEN column_name1 AND column_name2;

may be that is reason it is not working as you expected

edited May 23 '17 at 12:23

Community

1
1

answered Jul 01 '13 at 07:00

Salil

46,566
21
122
156

This is another way of doing it, but it doesn't explain why `DATE(?) BETWEEN start_date AND end_date` doesn't give the expected result. – Mischa Jul 01 '13 at 07:03
No, the other way around should work fine too. I just tried `DATE(?) BETWEEN start_date AND end_date` myself and it works fine. – Mischa Jul 01 '13 at 07:23
@Mischa :- can you provide any reference link which confirms this, so that it helps everyone? – Salil Jul 01 '13 at 07:24
Also, Why there is -ve marking, will some one bother to comment why it's not correct before marked answer to negative? – Salil Jul 01 '13 at 07:30
I downvoted and left multiple comments. Reason for downvote: your claim that his "between clause" is wrong is not true. [Here](http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_between) is the documentation for the `BETWEEN` operator on MySQL. It talks about `BETWEEN` in terms of expressions and arguments, which can be anything (including columns). Anyway, just try it out for yourself and you'll see it works fine. – Mischa Jul 01 '13 at 07:35
@Mischa:- Sorry but i didn't get single example which states that we can use columns. – Salil Jul 01 '13 at 07:50
[Here's an example](http://stackoverflow.com/a/7190939/84631) and [here's another example](http://stackoverflow.com/a/3746859/84631) by a 115k user. And [here's](http://www.w3fools.com/) why you shouldn't use w3schools as a reference. By the way, have you tried it yet?? – Mischa Jul 01 '13 at 09:18
@Salil i already tried the solution before writing this question your solution is alternative but i am not getting the result from this code also – hiteshtr Jul 01 '13 at 10:46

score 0 · Answer 3 · answered Feb 05 '15 at 13:07

You could use below gem to find the records between dates,

This gem quite easy to use and more clear By star am using this gem and the API more clear and documentation also well explained.

MessPrice.today #take all the 2 days records

Here you could pass our field also MessPrice.by_month("January", field: :updated_at)

Please see the documentation and try it.

score -2 · Answer 4 · answered Jul 01 '13 at 07:17

-2

Basically you want to list the Mess price rate for record if that record is available for (say)sale between start date and end date.

I have used find_by_sql for this purpose, you could improve it further:

MessPrice.find_by_sql("SELECT * FROM mess_prices WHERE '#{Date.today}' BETWEEN start_date AND end_date")

answered Jul 01 '13 at 07:17

user2406618

144
1
2
12

Could you motive why you have to use `find_by_sql` for this instead of a normal `where`?? – Mischa Jul 01 '13 at 07:18

rails: select record if current date is between two date and show in view

4 Answers4