# Date Issues + Oracle + RoR

**URL:** <https://www.ruby-forum.com/t/date-issues-oracle-ror/119032>\
**Category:** Rails\
**Created:** [November 2, 2007, 12:00pm UTC](https://www.ruby-forum.com/t/date-issues-oracle-ror/119032 "2007-11-02T12:00:33Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Bala](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/94ad74/32.png) [@Bala](https://www.ruby-forum.com/u/Bala)\
**Post date:** [November 2, 2007, 12:00pm UTC](https://www.ruby-forum.com/t/date-issues-oracle-ror/119032/1 "2007-11-02T12:00:33Z")

</div>

Hi Experts

I’ve a product like this

ID -\> 1  
Name -\> Xyz  
Price -\> 30  
Created\_at -\> 02-NOV-07

from\_date = Date.new(2, 10, 07)  
to\_date = Date.new(2, 10, 07)

@product = Product.find\_by\_sql(“select \* from products where  
created\_at between from\_date and to\_date”)

code works perfectly, buy my issue is i dont get any data. always says  
no record found

but if i use as like this

from\_date = Date.new(2, 10, 07)  
to\_date = Date.new(3, 10, 07)

@product = Product.find\_by\_sql(“select \* from products where  
created\_at between from\_date and to\_date”)

then its working fine, i can get the datas.

any suggestions experts?

Thanks in advance,

Bala

---

<div class="post-metadata">

**Author:** ![Bala](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/94ad74/32.png) [@Bala](https://www.ruby-forum.com/u/Bala)\
**Post date:** [December 4, 2007, 3:36am UTC](https://www.ruby-forum.com/t/date-issues-oracle-ror/119032/2 "2007-12-04T03:36:40Z")

</div>

On Nov 2, 8:59 pm, Bala [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> to\_date = Date.new(2, 10, 07)  
> to\_date = Date.new(3, 10, 07)  
> Bala  
> Assuming you mean

from\_date = to\_date = Date.new(2,11,07) # NOV, not OCT

the problem would seem to be what you are actually asking.

Oracle’s DATE includes time to the second, so when Rails passes the  
Date object through to Oracle (via oci/oracle adapter), it ends up as

to\_date(‘2007-11-02 00:00:00’, ‘yyyy-mm-dd hh24:mi:ss’)

so your from\_date and to\_date variables are both set to MIDNIGHT at  
the start of the day. The created\_at variable is a timestamp (in Rails  
and Oracle), so includes hours, minutes and seconds (and fractional  
seconds).

I’d suggest that if you use created\_on instead of created\_at you would  
probably find that it works as expected (assuming you don’t need any  
more explicit ordering within each day). Since you know that

from\_date = Date.new(3,11,07)

is midnight at the start of 3/11/07, you can use it this way also.

Cheers,  
Matt

---

<div class="post-metadata">

**Author:** ![Bala](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/94ad74/32.png) [@Bala](https://www.ruby-forum.com/u/Bala)\
**Post date:** [December 4, 2007, 12:28pm UTC](https://www.ruby-forum.com/t/date-issues-oracle-ror/119032/3 "2007-12-04T12:28:24Z")

</div>

Try this way if it works??

@product = Product.find\_by\_sql(“select \* from products where  
date(created\_at) between from\_date and to\_date”)

if u use date(created\_at) then it will start from that date only…

---

<div class="post-metadata">

**Author:** ![Bala](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/94ad74/32.png) [@Bala](https://www.ruby-forum.com/u/Bala)\
**Post date:** [December 5, 2007, 10:55am UTC](https://www.ruby-forum.com/t/date-issues-oracle-ror/119032/4 "2007-12-05T10:55:49Z")

</div>

Hi,

In Oracle, you have to use trunc() rather than date() to extract the  
date part of a date or datetime expression.

–  
Cheers,  
Jesse

2007/12/4, Naga harish Kanegolla [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid):