find_by_sql and date_format

F

felix sheng

Hello,

I'm beginning my first project in Ruby (and Rails) and running into
an issue. I need to pull some data out of mysql using the date_format
command but ActiveRecord is not appreciating my sql. I'm doing
something like:

Table.find_by_sql( [ 'select column1, date_format("%Y%m%d", column2)
as column2 from table where column1 = ?', some_data ] );

More or less it looks like that. The key problem seems to be the
date_format has %'s in it and it gives me:

malformed format string - %Y

And the trace:

/usr/local/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/
active_record/base.rb:1418:in `%'
/usr/local/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/
active_record/base.rb:1418:in `sanitize_sql_array'
/usr/local/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/
active_record/base.rb:1387:in `sanitize_sql'
/usr/local/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/
active_record/base.rb:427:in `find_by_sql'

Is there any way to handle this situation? I also tried using the raw
connection, but that didn't seem to take bind variables which I found
unsatisfying, so didn't pursue it too closely.

Any help/pointers are greatly appreciated.

felix
 
A

Alex LeDonne

Hello,

I'm beginning my first project in Ruby (and Rails) and running into
an issue. I need to pull some data out of mysql using the date_format
command but ActiveRecord is not appreciating my sql. I'm doing
something like:

Table.find_by_sql( [ 'select column1, date_format("%Y%m%d", column2)
as column2 from table where column1 = ?', some_data ] );

More or less it looks like that. The key problem seems to be the
date_format has %'s in it and it gives me:

malformed format string - %Y

And the trace:

/usr/local/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/
active_record/base.rb:1418:in `%'
/usr/local/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/
active_record/base.rb:1418:in `sanitize_sql_array'
/usr/local/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/
active_record/base.rb:1387:in `sanitize_sql'
/usr/local/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/
active_record/base.rb:427:in `find_by_sql'

Is there any way to handle this situation? I also tried using the raw
connection, but that didn't seem to take bind variables which I found
unsatisfying, so didn't pursue it too closely.

Any help/pointers are greatly appreciated.

felix

Felix,

According to http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html
, the function should be called as:

DATE_FORMAT(date,format)

That is, the format is the SECOND argument.

-A
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top