hi list,
i'm using the oci adapter to talk to Oracle 9i on Win XP Pro, ruby
185-21, rails 1.2.1
my controller has this code (simplified for testing):
sql = "SELECT * FROM reviews WHERE ((created_at between '06feb07'
and '07feb07'))"
@reviews = Review.find_by_sql(sql)
logger.debug("controller found #{@reviews.length} reviews")
the log output is such:
[4;35;1mReview Load (0.030000) [0m [0mSELECT * FROM reviews WHERE
((created_at between '06feb07' and '07feb07'))
controller found 0 reviews
the problem is that if i cut/paste that SQL statement into TOAD, i get
the six rows I expect to match. running it through rails is returning
zero results.
what's going wrong here?
I would also try it from SQLPlus, not
just Toad. By the way, those are strange looking dates. Don’t you need
separators?
Regards,
Dave
Just guessing that this is a date format issue. Try your dates as
2007-02-07 00:00:00
David Dumaresq wrote:
mike and david,
thanks for the reply. regarding the date format, oracle likes it that
way (at least in toad) or else you have to convert it using to_date().
i did try using iso format too, same zero results via rails and a
format exception via toad.
i suppose i could try to build the query using to_date()... didn't
think of that before.
just a follow up: using oracle's to_date() function does work:
date = Date.parse(params[:created_at])
sql = "select * from reviews where (created_at between
to_date('#{date.strftime('%m/%d/%Y')}', 'MM/DD/YYYY') and
to_date('#{date.+(1).strftime('%m/%d/%Y')}', 'MM/DD/YYYY'))"
@reviews = Review.find_by_sql(sql)
not 100% ideal i suppose since this hardcodes database-specific sql in
the controller, but then again there's no chance we'll be switching
databases 
