search for dates in oracle not working?

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?



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 :slight_smile: