0

I'm trying to get a page to display a list of all the records that are valid at the current time, so comparing the start and end time & date to the time at that current moment. This is the code I have at the moment:

def self.time
find_by_sql("SELECT *
FROM screens s
WHERE (s.starttime < CONVERT(varchar(30), GETDATE(), 114)) AND (s.finishtime > CONVERT(varchar(30), GETDATE(), 114))")
end

However I get this: ActiveRecord::StatementInvalid

I know my code isn't right.. I just don't quite know how to fix it?

user1738017
  • 609
  • 2
  • 12
  • 29

1 Answers1

0
def self.time
  find_by_sql("SELECT *
  FROM screens s
  WHERE (s.starttime < #{Time.now}) AND (s.finishtime >  #{Time.now}")
end
Sachin R
  • 11,606
  • 10
  • 35
  • 40
  • still says statement invalid `Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '10:14:45 +0000) AND (s.finishtime > 2013-01-08 10:14:45 +0000' at line 3: SELECT * FROM screens s WHERE (s.starttime < 2013-01-08 10:14:45 +0000) AND (s.finishtime > 2013-01-08 10:14:45 +0000` – user1738017 Jan 08 '13 at 10:15
  • I think we will miss the single quote(') in the time. like this ' 2013-01-08 10:14:45 +0000' – Sachin R Jan 08 '13 at 10:25