MySQL error comparing dates

Question

I'm trying to query my database and return with entries that are within a certain date range. As a possible simple example consider the following:

@foos = FooTracking.where('when > ?', DateTime.new(2008, 12, 22, 14, 30))

Which causes an error and dumps:

ActiveRecord::StatementInvalid: Mysql::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 'when >= '2008-12-22 14:30:00')' at line 1:
SELECT `foo_tracking`.* FROM `foo_tracking` WHERE (when >= '2008-12-22 14:30:00')

The where call works on other columns of my table (none that have anything to do with dates), what am I missing here?

tgies · Accepted Answer · 2011-01-28 22:30:40Z

5

WHEN is a SQL reserved word. You can still use it as a column name if you always mention it in backticks like this: `when`.

answered Jan 28, 2011 at 22:30

tgies

7054 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

keybored Over a year ago

Like: @foos = FooTracking.where(''when' > ?', DateTime.new(2008, 12, 22, 14, 30))?

keybored Over a year ago

Oh wait no, you mean the accent mark in tilda. Totally did the trick, thanks!

klochner Over a year ago

he should just rename the column, that's definitely going to bite him again in the future

tgies Over a year ago

Yes, I really would suggest renaming the column, especially if you're using a database abstraction layer that is building queries for you. You might not always be able to manually insert backtics like that.

keybored Over a year ago

Oops, I guess that non-reaction by the check mark was just it taking a while to check. Had clicked it twice heh.

Collectives™ on Stack Overflow

MySQL error comparing dates

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related