Date Issues + Oracle + RoR

[Bala]

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

[Bala]

On Nov 2, 8:59 pm, Bala [email protected] 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

[Bala]

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…

[Bala]

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 [email protected]: