Topic: Strange PG Error

I am trying to the the events in a date time and i get the date no problem but when i get to the time is just gives me a error

def self.search2(datetime)
    if datetime
      where('(start_date <= ? AND end_date >= ?) AND (start_time <= ? AND end_date >= ? )', "%#{datetime}%", "%#{datetime}%", "%#{datetime.try(:strftime ,'%H:%M')}%" , "%#{datetime.try(:strftime ,'%H:%M')}%" )
    else
      scoped
    end
  end

PG::Error: ERROR:  invalid input syntax for type date: "%18:06%"
LINE 1: ...0%') AND (start_time <= '%18:06%' AND end_date >= '%18:06%' ...
                                                             ^
: SELECT COUNT(*) FROM "oncalls"  WHERE ((start_date <= '%2012-07-05T18:06:40-04:00%' AND end_date >= '%2012-07-05T18:06:40-04:00%') AND (start_time <= '%18:06%' AND end_date >= '%18:06%' ))

Re: Strange PG Error

As far as I know, you can't use % except in LIKE/ILIKE. Also, not sure if you intended this, but you use start date and end date in the first part, and START_TIME and end date in the second.

Re: Strange PG Error

I was wondering how i can output the sql

Last edited by moiseszaragoza (2012-07-06 12:01:03)

Re: Strange PG Error

If you have an ActiveRecord::Relation (ie result of .where), call .to_sql to see the query as a string.

I'm a Phoenix Ruby on Rails developer currently available for freelance work.