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?
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
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 <jemminger-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
Sent by: rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
17-04-2007 12:17 PM
Please respond to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To
"Ruby on Rails: Talk"
<rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org>
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?
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
Just guessing that this is a date format issue. Try your dates as 2007-02-07 00:00:00 David Dumaresq wrote:> > 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 <jemminger-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>* > Sent by: rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org > > 17-04-2007 12:17 PM > Please respond to > rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org > > > > To > "Ruby on Rails: Talk" <rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org> > 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? > > > > > > >--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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. On Apr 17, 7:23 pm, "Michael A. Schoen" <scho...-ihVZJaRskl1bRRN4PJnoQQ@public.gmane.org> wrote:> Just guessing that this is a date format issue. Try your dates as > > 2007-02-07 00:00:00 > > David Dumaresq wrote: > > > 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--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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 :-)
On Apr 17, 9:57 pm, jemminger
<jemmin...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
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.
>
> On Apr 17, 7:23 pm, "Michael A. Schoen"
<scho...-ihVZJaRskl1bRRN4PJnoQQ@public.gmane.org> wrote:
>
> > Just guessing that this is a date format issue. Try your dates as
>
> > 2007-02-07 00:00:00
>
> > David Dumaresq wrote:
>
> > > 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
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---