Search between dates won't work properly

Rails
1

I’d like to search for invoices in the database, having the date range
set (from, to). The problem is that invoices whose issue dates match the
‘from’ or ‘to’ dates will not show up in results.

For example:
Created an invoice today (“2009-02-09”), and I’m searching by issue_date
between from and to, these are the params:

“filter”=>{“from”=>“2009-02-09”, “to”=>“2009-02-09”}

In the model I have the sql conditions, as well as a scope, looks like
this:

self.cond = Array.new
self.cond << [“invoices.issue_date >= ?”, from] unless from.blank?
self.cond << [“invoices.issue_date <= ?”, to] unless to.blank?
result = join_conditions

named_scope :filter, lambda { |invoice_filter|
{ :conditions => invoice_filter.sql_conditions }}

The scope gets a filter object that has both ‘from’ and ‘to’ fields set.
If I set the ‘to’ field to tomorrow’s date, then the invoice will
appear, but I don’t understand why it wouldn’t work as is since there
are ‘<=’ & ‘>=’ comparisons in conditions.

Any help or suggestion would be appreciated!

2

How about:

…
require ‘time’
…
invoices = Invoice.find(:all, :conditions=>[‘issue_date is not null
and (issue_date >= ? or issue_date <= ?)’, Time.parse(‘2009-02-09
00:00:00’), Time.parse(‘2009-02-09 23:59:59’)] :order=>‘issue_date’)
…

Jeff

3

things = Thing.find_all_by_field(‘start_at’…‘end_at’)

4

Jeff B.systems wrote:

How about:

…
require ‘time’
…
invoices = Invoice.find(:all, :conditions=>[‘issue_date is not null
and (issue_date >= ? or issue_date <= ?)’, Time.parse(‘2009-02-09
00:00:00’), Time.parse(‘2009-02-09 23:59:59’)] :order=>‘issue_date’)
…

Jeff

Thanks for your reply, and your help! It gave me a hint and solved the
problem like this:

self.cond << [“invoices.issue_date > ?”, from - 1] unless from.blank?
self.cond << [“invoices.issue_date < ?”, to + 1] unless to.blank?

because issue_date is a datetime, and generating an invoice means saving
both date and time of the day. This way the search will have the same
results as in your example.

Once again, thank you for your help!

5

On 9 Feb 2009, at 17:29, Gabi Ge wrote:

“filter”=>{“from”=>“2009-02-09”, “to”=>“2009-02-09”}

Is the issue date a date column or a datetime column ?
if you have a datetime column containing 2009-02-09 14:00 and compare
it to 2009-02-09 then the 2009-02-09 is turned into 2009-02-09 00:00
which can mess things up for you.

Fred

6

I didn’t know you could pass an array of arrays!

Blog: http://random8.zenunit.com/
Learn rails: http://sensei.zenunit.com/

On 10/02/2009, at 6:29 AM, Gabi Ge [email protected]