SQL query error for datetime object

Question

I am trying to execute this query into my sql database

'INSERT INTO ba_shop_opening (day, from, to) VALUES (?, ?, ?)' with params ["mon", "1970-01-01 00:00:00", "1970-01-01 00:00:00"]:

and I am getting the following error

Syntax error or access violation: 1064 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 'from, to) VALUES ('mon', '1970-01-01 00:00:00', '1970-01-01 00:00:00')' at line 1

Here is the DDL of the table I am working with

CREATE TABLE `ba_shop_opening` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `from` datetime NOT NULL,
  `to` datetime NOT NULL,
  'day' VARCHAR(20) NOT NULL
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

I don't understand what the problem. Any help will be appreciated.

And backticks or not, don't use reserved words as table/column names. — Strawberry
– Strawberry, Commented Sep 11, 2016 at 18:59

benni_mac_b · Accepted Answer · 2016-09-11 18:47:44Z

5

from is a reserved word. I'd recommend you rename the column in the table.

https://dev.mysql.com/doc/refman/5.7/en/keywords.html

answered Sep 11, 2016 at 18:47

benni_mac_b

8,9075 gold badges43 silver badges60 bronze badges

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

2 Comments

Miguel Carvajal Over a year ago

ahahah, also TO and DAY are reserved kekwords. I just updated the name of the columns and it worked. By the way, the error messages in MySQL sucks! Thanks for your help.

spencer7593 Over a year ago

@krvajal: yes, DAY is a keyword in MySQL, but it's not reserved word.

Gordon Linoff · Accepted Answer · 2016-09-11 19:07:05Z

from and to are both reserved words.

In addition, although date is not reserved, it is both a function and a data type.

Also, when declaring columns in a table, you should not use single quotes. Only use single quotes for string and date constants -- to avoid unexpected errors.

Try this:

CREATE TABLE `ba_shop_opening` (
  id int(11) NOT NULL AUTO_INCREMENT,
  from_dt datetime NOT NULL,
  to_dt datetime NOT NULL,
  day_of_week VARCHAR(20) NOT NULL
  PRIMARY KEY (id)
) ;

INSERT INTO ba_shop_opening (day_of_week, from_dt, to_dt)
    VALUES (?, ?, ?);

The column names are cleaner and you don't need to escape any names.

I might suggest that the datetime columns are really times, but you don't fully explain the data structure.

Collectives™ on Stack Overflow

SQL query error for datetime object

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related