# Search between dates won't work properly

**URL:** <https://www.ruby-forum.com/t/search-between-dates-wont-work-properly/159880>\
**Category:** Rails\
**Created:** [February 9, 2009, 6:29pm UTC](https://www.ruby-forum.com/t/search-between-dates-wont-work-properly/159880 "2009-02-09T18:29:45Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![gabi](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/f07891/32.png) [@gabi](https://www.ruby-forum.com/u/gabi)\
**Post date:** [February 9, 2009, 6:29pm UTC](https://www.ruby-forum.com/t/search-between-dates-wont-work-properly/159880/1 "2009-02-09T18:29:45Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![gabi](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/f07891/32.png) [@gabi](https://www.ruby-forum.com/u/gabi)\
**Post date:** [February 9, 2009, 7:00pm UTC](https://www.ruby-forum.com/t/search-between-dates-wont-work-properly/159880/2 "2009-02-09T19:00:11Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![gabi](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/f07891/32.png) [@gabi](https://www.ruby-forum.com/u/gabi)\
**Post date:** [February 9, 2009, 8:16pm UTC](https://www.ruby-forum.com/t/search-between-dates-wont-work-properly/159880/3 "2009-02-09T20:16:06Z")

</div>

things = Thing.find\_all\_by\_field(‘start\_at’…‘end\_at’)

---

<div class="post-metadata">

**Author:** ![gabi](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/f07891/32.png) [@gabi](https://www.ruby-forum.com/u/gabi)\
**Post date:** [February 9, 2009, 8:29pm UTC](https://www.ruby-forum.com/t/search-between-dates-wont-work-properly/159880/4 "2009-02-09T20:29:02Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![gabi](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/f07891/32.png) [@gabi](https://www.ruby-forum.com/u/gabi)\
**Post date:** [February 10, 2009, 1:07am UTC](https://www.ruby-forum.com/t/search-between-dates-wont-work-properly/159880/5 "2009-02-10T01:07:12Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![gabi](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/f07891/32.png) [@gabi](https://www.ruby-forum.com/u/gabi)\
**Post date:** [February 10, 2009, 5:04am UTC](https://www.ruby-forum.com/t/search-between-dates-wont-work-properly/159880/6 "2009-02-10T05:04:55Z")

</div>

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

Blog: [http://random8.zenunit.com/](http://random8.zenunit.com/)  
Learn rails: [http://sensei.zenunit.com/](http://sensei.zenunit.com/)

On 10/02/2009, at 6:29 AM, Gabi Ge [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)