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?

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