# Search for dates in oracle not working? **URL:** \ **Category:** Rails\ **Created:** [April 17, 2007, 9:18pm UTC](https://www.ruby-forum.com/t/search-for-dates-in-oracle-not-working/92288 "2007-04-17T21:18:19Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![jemminger](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/c0e974/32.png) [@jemminger](https://www.ruby-forum.com/u/jemminger)\ **Post date:** [April 17, 2007, 9:18pm UTC](https://www.ruby-forum.com/t/search-for-dates-in-oracle-not-working/92288/1 "2007-04-17T21:18:19Z")
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? ---
**Author:** ![jemminger](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/c0e974/32.png) [@jemminger](https://www.ruby-forum.com/u/jemminger)\ **Post date:** [April 17, 2007, 11:48pm UTC](https://www.ruby-forum.com/t/search-for-dates-in-oracle-not-working/92288/2 "2007-04-17T23:48:39Z")
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 [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) Sent by: removed\_email\_address@domain.invalid 17-04-2007 12:17 PM Please respond to removed\_email\_address@domain.invalid To “Ruby on Rails: Talk” [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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? ---
**Author:** ![jemminger](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/c0e974/32.png) [@jemminger](https://www.ruby-forum.com/u/jemminger)\ **Post date:** [April 18, 2007, 1:23am UTC](https://www.ruby-forum.com/t/search-for-dates-in-oracle-not-working/92288/3 "2007-04-18T01:23:45Z")
Just guessing that this is a date format issue. Try your dates as 2007-02-07 00:00:00 ---
**Author:** ![jemminger](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/c0e974/32.png) [@jemminger](https://www.ruby-forum.com/u/jemminger)\ **Post date:** [April 18, 2007, 3:58am UTC](https://www.ruby-forum.com/t/search-for-dates-in-oracle-not-working/92288/4 "2007-04-18T03:58:52Z")
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. ---
**Author:** ![jemminger](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/c0e974/32.png) [@jemminger](https://www.ruby-forum.com/u/jemminger)\ **Post date:** [May 1, 2007, 3:03pm UTC](https://www.ruby-forum.com/t/search-for-dates-in-oracle-not-working/92288/5 "2007-05-01T15:03:01Z")
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 🙂