Rails equivalent to mysql date() function?

Rails
1

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

2

On Nov 16, 2007 4:39 PM, Anthony [email protected] wrote:

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

3

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?

4

Why can’t you just use mysql’s date function?

Event.find(:all, :conditions => [‘date(datefield) = ?’,“11/13/2007”]

-Bill

Anthony wrote:

Jason

–
Sincerely,

William P.

5

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 C. [email protected]

6

On 16 Nov 2007, at 23:57, William P. 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

7

Thanks! this got it.

-Anthony

On Nov 18, 6:33 am, Frederick C. [email protected]