# Find\_by\_sql and date\_format

**URL:** <https://www.ruby-forum.com/t/find-by-sql-and-date-format/90112>\
**Category:** Ruby\
**Created:** [March 26, 2007, 4:19am UTC](https://www.ruby-forum.com/t/find-by-sql-and-date-format/90112 "2007-03-26T04:19:43Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![felix\_sheng](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/e56c9b/32.png) [@felix\_sheng](https://www.ruby-forum.com/u/felix_sheng)\
**Post date:** [March 26, 2007, 4:19am UTC](https://www.ruby-forum.com/t/find-by-sql-and-date-format/90112/1 "2007-03-26T04:19:43Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![felix\_sheng](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/e56c9b/32.png) [@felix\_sheng](https://www.ruby-forum.com/u/felix_sheng)\
**Post date:** [March 26, 2007, 7:51pm UTC](https://www.ruby-forum.com/t/find-by-sql-and-date-format/90112/2 "2007-03-26T19:51:11Z")

</div>

On 3/25/07, felix sheng [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> More or less it looks like that. The key problem seems to be the  
> /usr/local/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/  
> felix

Felix,

According to  
[http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html](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