datetime select

rubyonrails-talk
1

I am having difficulty passing datetime values to a method in the controller. I get sql syntax error from the following. Can someone advise me what I am doing wrong?

my view:

    <table>       <tr>         <%= start_form_tag :action => 'time_range' %>         <td ><%= datetime_select("sd", "time_range") %></td>         <td ><%= datetime_select("ed", "time_range") %></td>         <td><%= submit_tag 'go'%></td>       </tr>     </table>

my method:

def time_range   start_date=params[:sd]   end_date=params[:ed]   @orders=Order.find(:all,:conditions => ['created_at BETWEEN ? AND ?', start_date, end_date])

end

2

hi,

you can do this,

@start_date = Date.civil(params[:range] [:"start_date(1i)"].to_i,params[:range] [:"start_date(2i)"].to_i,params[:range][:"start_date(3i)"].to_i)             @end_date = Date.civil(params[:range] [:"end_date(1i)"].to_i,params[:range] [:"end_date(2i)"].to_i,params[:range][:"end_date(3i)"].to_i)

thanks and regards, shripad

3

Thanks Shripad From your advice I've got the following. It worked fine until I added hours and minutes to the range which are attributes 4i and 5i. I don't get any error messages but don't get values from db either. Datetime format in the db is 2003-10-14 12:37:51. Any suggestions would be appreciated.

my view:

    <table>       <tr>         <%= start_form_tag :action => 'time_range' %>         <td ><%= datetime_select("sd", "range") %></td>         <td ><%= datetime_select("ed", "range") %></td>         <td><%= submit_tag 'go'%></td>       </tr>     </table>

my method:

def time_range   @start_date = DateTime.civil(params[:sd][:"range(1i)"].to_i, params[:sd][:"range(2i)"].to_i, params[:sd][:"range(3i)"].to_i,params[:sd][:"range(4i)"].to_i, params[:sd][:"range(5i)"].to_i)

  @end_date = DateTime.civil(params[:ed][:"range(1i)"].to_i,params[:ed][:"range(2i)"].to_i,params[:ed][:"range(3i)"].to_i,params[:ed][:"range(4i)"].to_i, params[:ed][:"range(5i)"].to_i)

  @orders=Order.find(:all,:conditions => ['created_at BETWEEN ? AND ?', @start_date, @end_date])

end

4

Hi, First tell me which database u r using? And, Please execute the last query against database, check whether it is working fine or not

thanks and regards, Shripad

5

Thanks for reply Shripad;

Db is Mysql. Following is the sql running for the last query: 'select paydate,payamount,credit,paymenttype,memberid from payments where paydate between '2008-10-09T14:22:00+00:00' AND '2008-10-10T14:22:00+00:00'

Format in db is 2003-10-14 12:37:51

Katsuo

6

Greetings Everyone…

In the conditions of your query you should pass the parameters as simple strings. User strftime to get the desired string representation while passing it to the query I’d try this:

@orders=Order.find(:all,:conditions => [‘created_at BETWEEN “?” AND “?”’,

start_date.strftime(‘%Y-%m-%d %H:%M:%S’), end_date.strftime(‘%Y-%m-%d %H:%M:%S’)])

the format above is the one u mentioned as the default of your database engine “Format in db is 2003-10-14 12:37:51”

7

On 9 Oct 2008, at 09:18, mahmoud said wrote:

Greetings Everyone..

In the conditions of your query you should pass the parameters as
simple strings. User strftime to get the desired string representation while passing
it to the query I'd try this:

@orders=Order.find(:all,:conditions => ['created_at BETWEEN "?" AND
"?"', start_date.strftime('%Y-%m-%d %H:%M:%S'), end_date.strftime('%Y-%m- %d %H:%M:%S')])

the format above is the one u mentioned as the default of your
database engine "Format in db is 2003-10-14 12:37:51"

Looks like you're using rails 1.1 or earlier. In more recent versions
of rails you don't need to do any of this:

Order.find(:all,:conditions => ['created_at BETWEEN ?
AND ?',start_date, end_date]) would just work

start_date.to_s(:db) might work, but I really can't remember what
works on really old versions of rails.

Fred

8

Thanks for the tip Fred.

I hadn’t tried Order.find(:all,:conditions => ['created_at BETWEEN ?

AND ?',start_date, end_date]) myself… it does work on rails 2.1.0

Mahmoud

9

I've setteled for the following and works great!

@orders=Order.find(:all,:conditions => ['created_at BETWEEN "?" AND "?"', start_date.strftime('%Y-%m-%d %H:%M:%S'), end_date.strftime('%Y-%m-%d %H:%M:%S')])

I am using 1.8 but somehow just start_date and end_date without formatting did not work.

BIG THANKS TO YOU ALL Katsuo

10

That's ruby 1.8. Rails version are independant of that

Fred