Hi, The following sql query works in mysql:>> SELECT * FROM events WHERE date(datefield) = ''2007-11-13''for selecting the date part of a datetime field. I need something that works in Rails. I tried to pass the query using:>> Event.find_by_sql("SELECT * FROM events WHERE date(datefield) = ''2007-11-13''")which works in the console!? but not when run inside my Rails app. Any clues? Thank you, Anthony --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
On Nov 16, 2007 4:39 PM, Anthony <bonesreview-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > Hi, The following sql query works in mysql: > > >> SELECT * FROM events WHERE date(datefield) = ''2007-11-13'' > > for selecting the date part of a datetime field. I need something that > works in Rails. I tried to pass the query using: > > >> Event.find_by_sql("SELECT * FROM events WHERE date(datefield) > ''2007-11-13''") > > which works in the console!? but not when run inside my Rails app. > > Any clues? > > Thank you, > >Let ActiveRecord do the work for you: Event.find(:all, :conditions => {:date => Date.parse("11/13/2007")}) or Event.find_all_by_date(Date.parse("11/13/2007")) Jason --~--~---------~--~----~------------~-------~--~----~ 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 tried this method, but because 11/13 has time associated with it, it won''t find it. If I use a date that has 00:00:00 as the time, it finds it. That''s why I had to use the date() method in mysql, because it only exposes the date part of the field to the query. Does that make sense? I need to be able to find all records with that date regardless of the times assigned. Thank you for your quick response, is there anything else I can do? On Nov 16, 3:42 pm, "Jason Roelofs" <jameskil...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On Nov 16, 2007 4:39 PM, Anthony <bonesrev...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > > > > > Hi, The following sql query works in mysql: > > > >> SELECT * FROM events WHERE date(datefield) = ''2007-11-13'' > > > for selecting the date part of a datetime field. I need something that > > works in Rails. I tried to pass the query using: > > > >> Event.find_by_sql("SELECT * FROM events WHERE date(datefield) > > ''2007-11-13''") > > > which works in the console!? but not when run inside my Rails app. > > > Any clues? > > > Thank you, > > Let ActiveRecord do the work for you: > > Event.find(:all, :conditions => {:date => Date.parse("11/13/2007")}) > > or > > Event.find_all_by_date(Date.parse("11/13/2007")) > > Jason--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Why can''t you just use mysql''s date function? Event.find(:all, :conditions => [''date(datefield) = ?'',"11/13/2007"] -Bill Anthony wrote:> I tried this method, but because 11/13 has time associated with it, it > won''t find it. If I use a date that has 00:00:00 as the time, it finds > it. That''s why I had to use the date() method in mysql, because it > only exposes the date part of the field to the query. Does that make > sense? I need to be able to find all records with that date regardless > of the times assigned. > > Thank you for your quick response, is there anything else I can do? > > On Nov 16, 3:42 pm, "Jason Roelofs" <jameskil...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > >> On Nov 16, 2007 4:39 PM, Anthony <bonesrev...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: >> >> >> >> >> >> >>> Hi, The following sql query works in mysql: >>> >>>>> SELECT * FROM events WHERE date(datefield) = ''2007-11-13'' >>>>> >>> for selecting the date part of a datetime field. I need something that >>> works in Rails. I tried to pass the query using: >>> >>>>> Event.find_by_sql("SELECT * FROM events WHERE date(datefield) >>>>> >>> ''2007-11-13''") >>> >>> which works in the console!? but not when run inside my Rails app. >>> >>> Any clues? >>> >>> Thank you, >>> >> Let ActiveRecord do the work for you: >> >> Event.find(:all, :conditions => {:date => Date.parse("11/13/2007")}) >> >> or >> >> Event.find_all_by_date(Date.parse("11/13/2007")) >> >> Jason >> > > >-- Sincerely, William Pratt --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
On 16 Nov 2007, at 23:57, William Pratt wrote:> Why can''t you just use mysql''s date function? > > Event.find(:all, :conditions => [''date(datefield) = ?'',"11/13/2007"]the disadvantage of that is that (on mysql at least), that won''t use an index on datefield. Something like Event.find(:all, :conditions => [''date_field>= ? and date_field < ?'', ''2007-11-13'', ''2007-11-14''] will Fred> -Bill > > Anthony wrote: >> >> I tried this method, but because 11/13 has time associated with it, >> it >> won''t find it. If I use a date that has 00:00:00 as the time, it >> finds >> it. That''s why I had to use the date() method in mysql, because it >> only exposes the date part of the field to the query. Does that make >> sense? I need to be able to find all records with that date >> regardless >> of the times assigned. >> >> Thank you for your quick response, is there anything else I can do? >> >> On Nov 16, 3:42 pm, "Jason Roelofs" <jameskil...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: >> >>> On Nov 16, 2007 4:39 PM, Anthony <bonesrev...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: >>> >>> >>> >>> >>> >>> >>>> Hi, The following sql query works in mysql: >>>> >>>>>> SELECT * FROM events WHERE date(datefield) = ''2007-11-13'' >>>>>> >>>> for selecting the date part of a datetime field. I need something >>>> that >>>> works in Rails. I tried to pass the query using: >>>> >>>>>> Event.find_by_sql("SELECT * FROM events WHERE date(datefield) >>>>>> >>>> ''2007-11-13''") >>>> >>>> which works in the console!? but not when run inside my Rails app. >>>> >>>> Any clues? >>>> >>>> Thank you, >>>> >>> Let ActiveRecord do the work for you: >>> >>> Event.find(:all, :conditions => {:date => Date.parse("11/13/2007")}) >>> >>> or >>> >>> Event.find_all_by_date(Date.parse("11/13/2007")) >>> >>> Jason >>> >> > > -- > Sincerely, > > William Pratt > > >--~--~---------~--~----~------------~-------~--~----~ 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 don''t use the string as a date, personally, but create a timestamp using Time.gm and pass that into my SQL query. This means I *know* exactly what''s being passed through with no parsing of UK/US/Whatever standard dates. In similar vein I''ve truncated by to_date on the Ruby timestamp rather than in the query text. When it is converted it has the midnight time. HTH On Nov 18, 12:33 pm, Frederick Cheung <frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On 16 Nov 2007, at 23:57, William Pratt wrote: > > > Why can''t you just use mysql''s date function? > > > Event.find(:all, :conditions => [''date(datefield) = ?'',"11/13/2007"] > > the disadvantage of that is that (on mysql at least), that won''t use > an index on datefield. > > Something like > Event.find(:all, :conditions => [''date_field>= ? and date_field < ?'', > ''2007-11-13'', ''2007-11-14''] > will > > Fred > > > -Bill > > > Anthony wrote: > > >> I tried this method, but because 11/13 has time associated with it, > >> it > >> won''t find it. If I use a date that has 00:00:00 as the time, it > >> finds > >> it. That''s why I had to use the date() method in mysql, because it > >> only exposes the date part of the field to the query. Does that make > >> sense? I need to be able to find all records with that date > >> regardless > >> of the times assigned. > > >> Thank you for your quick response, is there anything else I can do? > > >> On Nov 16, 3:42 pm, "Jason Roelofs" <jameskil...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > >>> On Nov 16, 2007 4:39 PM, Anthony <bonesrev...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > >>>> Hi, The following sql query works in mysql: > > >>>>>> SELECT * FROM events WHERE date(datefield) = ''2007-11-13'' > > >>>> for selecting the date part of a datetime field. I need something > >>>> that > >>>> works in Rails. I tried to pass the query using: > > >>>>>> Event.find_by_sql("SELECT * FROM events WHERE date(datefield) > > >>>> ''2007-11-13''") > > >>>> which works in the console!? but not when run inside my Rails app. > > >>>> Any clues? > > >>>> Thank you, > > >>> Let ActiveRecord do the work for you: > > >>> Event.find(:all, :conditions => {:date => Date.parse("11/13/2007")}) > > >>> or > > >>> Event.find_all_by_date(Date.parse("11/13/2007")) > > >>> Jason > > > -- > > Sincerely, > > > William Pratt--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Thanks! this got it. -Anthony On Nov 18, 6:33 am, Frederick Cheung <frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On 16 Nov 2007, at 23:57, William Pratt wrote: > > > Why can''t you just use mysql''s date function? > > > Event.find(:all, :conditions => [''date(datefield) = ?'',"11/13/2007"] > > the disadvantage of that is that (on mysql at least), that won''t use > an index on datefield. > > Something like > Event.find(:all, :conditions => [''date_field>= ? and date_field < ?'', > ''2007-11-13'', ''2007-11-14''] > will > > Fred > > > -Bill > > > Anthony wrote: > > >> I tried this method, but because 11/13 has time associated with it, > >> it > >> won''t find it. If I use a date that has 00:00:00 as the time, it > >> finds > >> it. That''s why I had to use the date() method in mysql, because it > >> only exposes the date part of the field to the query. Does that make > >> sense? I need to be able to find all records with that date > >> regardless > >> of the times assigned. > > >> Thank you for your quick response, is there anything else I can do? > > >> On Nov 16, 3:42 pm, "Jason Roelofs" <jameskil...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > >>> On Nov 16, 2007 4:39 PM, Anthony <bonesrev...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > >>>> Hi, The following sql query works in mysql: > > >>>>>> SELECT * FROM events WHERE date(datefield) = ''2007-11-13'' > > >>>> for selecting the date part of a datetime field. I need something > >>>> that > >>>> works in Rails. I tried to pass the query using: > > >>>>>> Event.find_by_sql("SELECT * FROM events WHERE date(datefield) > > >>>> ''2007-11-13''") > > >>>> which works in the console!? but not when run inside my Rails app. > > >>>> Any clues? > > >>>> Thank you, > > >>> Let ActiveRecord do the work for you: > > >>> Event.find(:all, :conditions => {:date => Date.parse("11/13/2007")}) > > >>> or > > >>> Event.find_all_by_date(Date.parse("11/13/2007")) > > >>> Jason > > > -- > > Sincerely, > > > William Pratt--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---