Search for dates in oracle not working?

[jemminger]

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?

[jemminger]

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

---

Information and Educational Technology
Kwantlen University College - 604-599-2120
“So powerful is the light of unity that it can illuminate the whole
earth.” --Bahá'u’lláh

jemminger [email protected]
Sent by: [email protected]
17-04-2007 12:17 PM
Please respond to
[email protected]

To
“Ruby on Rails: Talk” [email protected]
cc

Subject
[Rails] 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?

[jemminger]

Just guessing that this is a date format issue. Try your dates as

2007-02-07 00:00:00

[jemminger]

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.

[jemminger]

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