Date Issues + Oracle + RoR

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

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

Hi,

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