Thoughts, information and reflections about technology

Rails – What to do check your date searches are not showing the results you expect.

Some dates will need to be stored in a date time format.  This does not pose a problem when you are searching for dates equal to or greater than a specific date. You also avoid problems if you are storing dates in a date format.

However, if you search for a date less than or equal to a specific date using a datetime column, your results do not include that date. In other words, if you set up a search to show you everything where the date in question was less than or equal to 3/25/2016 you would not get any records where the date was 3/25/2016.

Apparently a stored datetime of 2016-03-25 11:40 is NOT less than or equal to 2016-03-25. That is because the hours count.
I found that I had to modify the SQL statement to change the stored date/time to a date with a ‘cast as date’ command. ie use “cast (documents.created_at as date) instead of documents.created_at

  # ————————–  Begin created at dates —————————-    # Only one date is filled in:    documents = documents.where(“documents.created_at >= ?”, from_date) if from_date.present? and not to_date.present?    documents = documents.where(cast (documents.created_at as date) <= ?”, to_date) if to_date.present? and not from_date.present?
   # cast ([created_at] as date) <= ‘2016-03-25’
    # Both Dates are filled in    documents = documents.where(“documents.created_at >= ?”, from_date,)  if from_date.present? and to_date.present?    documents = documents.where(“cast (documents.created_at as date) <= ?”, to_date) if to_date.present? and from_date.present?

The cast (documents.created_at as date) will compare the dates as dates and ignore the time.


I did not use the cast as date on the from because I believe that 2016-03-25 11:40 IS >= 2016-03-25
This will not only apply to ruby projects but also to any sql tables where a date-time is stored and you are looking for items where the date is less than a specified date.

Similar Posts:

Leave a Reply

Your email address will not be published. Required fields are marked *

Contact me