Date Issues + Oracle + RoR

rubyonrails-talk
1

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

2

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

3

Hi,

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