Ruby on Rails, Datepicker search between two dates

I am a new ROR user trying to implement a DateTime picker to select dates for a search function.

My problem is that when I enter two dates (date from, and date to) in the DatePicker, I get inconsistent results from the search. Sometimes I get no results, and sometimes I get all the results. I am also using the bootstrap-datepicker-rails gem as well as the ransack gem.

This is what my view looks like

  <div class="field">
    <%= f.label :departure_date_gteq, "Departure Date Between" %>
    <%= f.search_field :departure_date_gteq, "data-provide" => "datepicker" %>
    <%= f.label :return_date_lteq, "and" %>
    <%= f.search_field :return_date_lteq, "data-provide" => "datepicker" %>
  </div>

The two database columns I am referencing are departure_date, and return_date. The date stored is datetime and looks like this,

departure_date
2016-08-07 00:00:00.000000

return_date
2016-08-14 00:00:00.000000

I enter 2016-08-06 and 2016-08-15 in the DatePickers and I am returned all the results.

Could my search form DatePicker be sending data that cannot be correctly compared to the DateTime in the database?

Am I using the 'gteq' and 'lteq' calls correctly?

EDIT #1 @Michael Gaskill, you are correct that the problem is the date formatting. When I enter a date manually, I get the correct search results. I just need to figure out how to correct the formatting before its passed to the controller.

Here is what my controller looks like.

class HomeController < ApplicationController

  def index
    @search = Sailing.search(params[:q])
    @sailings = @search.result
    ... Other calls ...
  end
end

Here is the full piece of code in my view that generates sailings content. Note that I'm using search_for_for which is part of the ransack gem.

<%= search_form_for @search, url: root_path, method: :post do |f| %>
          <div class="field">
            <%= f.label :cruise_ship_name_or_cruise_ship_company_or_destination_identifier_cont, "Cruise Ship Name" %>
            <%= f.text_field :cruise_ship_name_or_cruise_ship_company_or_destination_identifier_cont %>
          </div>
          <div class="field">
            <%= f.label :departure_date_gteq, "Departure Date Between" %>
            <%= f.search_field :departure_date_gteq, "data-provide" => "datepicker" %>
            <%= f.label :return_date_lteq, "and" %>
            <%= f.search_field :return_date_lteq, "data-provide" => "datepicker" %>
          </div>
          <div class="actions"><%= f.submit "Search" %></div>
      <% end %>

Thanks.

Solution

You need to convert the string-formatted dates entered in the form (either via the DatePicker or manually-entered) to Date or DateTime values. When you query the database, use this DateTime#strptime to convert:

MyTable.where(date_field_to_search: DateTime.strptime(date_value_from_ui, "%F %T")

You can check out the formats available as the second argument to strptime, using the instructions at DateTime:strftime. Note that strftime and strptime are used to convert back and forth between DateTime and formatted String values.

In your Sailing#search(params[:q]) example, you can either implement the solution in Sailing#search (if you have access to change that code) or prior to calling Sailing.search.

Here is how you might implement this in Sailing#search:

class Sailing
  def search(args)
    datetime = args[:date_field_to_search]
    if datetime.kind_of?(String)
      datetime = DateTime.strptime(datetime, "%F %T")
    end
    # other search functionality
  end
end

or, prior to calling Sailing#search:

datetime = params[:q][:date_field_to_search]
if datetime.kind_of?(String)
  datetime = DateTime.strptime(datetime, "%F %T")
end
Sailing.search(params[:q].merge({ date_field_to_search: datetime })