Cannot insert or update a datetime column with 'yyyy-MM-dd hh:mm:ss' format

Question

I've been trying to wrap my head around what the actual problem is. I'm thinking it might be collation, When I did

SELECT @@character_set_database, @@collation_database;

I got this

+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| latin1                   | latin1_swedish_ci    |
+--------------------------+----------------------+

the mysql workbench has utf8 - default collation as standard.

this is the table I want to update/insert into.

+------------+----------+------+-----+-------------------+----------------+
| Field      | Type     | Null | Key | Default           | Extra          |
+------------+----------+------+-----+-------------------+----------------+
| id         | int(11)  | NO   | PRI | NULL              | auto_increment |
| from       | datetime | YES  |     | NULL              |                |
| to         | datetime | YES  |     | NULL              |                |
| breakhours | float    | NO   |     | 0.5               |                |
| created    | datetime | NO   |     | CURRENT_TIMESTAMP |                |
| assignment | int(11)  | NO   | MUL | NULL              |                |
+------------+----------+------+-----+-------------------+----------------+

this is what happens when I try to insert an entry manually with the mysql console, doesn't work with mysql workbench either.

mysql> insert into TimeEntry(from, to, breakhours, assignment) values('2019-09-14 07:45', '2019-09-14 16:45', 0.5, 1);
ERROR 1064 (42000): 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, breakhours, assignment) values('2019-09-14 07:45', '2019-09-14 16:45',' at line 1

So I put an empty entry into the table

+----+------+------+------------+---------------------+------------+
| id | from | to   | breakhours | created             | assignment |
+----+------+------+------------+---------------------+------------+
|  2 | NULL | NULL |        0.5 | 2019-09-15 18:51:24 |          1 |
+----+------+------+------------+---------------------+------------+

but I can't even update from or to here.

mysql> update TimeEntry set from = '2019-09-14 07:45:00' where id = 2;
ERROR 1064 (42000): 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 = '2019-09-14 07:45:00' where id = 2' at line 1

I've been using MySql for years, I've never encountered this problem before, what am I doing wrong?

`From` is a reserved keyword in MySQL. Use backticks(`) to escape it. — Ankit Bajpai, Sep 15 '19 at 17:00

score 2 · Accepted Answer · edited Sep 15 '19 at 17:10

2

Escape from with backticks, it's a reserved word (as you can see from the syntax highlighting). You should escape to as well.

Infact, avoid using keywords for column names etc. Check complete list of keywords at: https://dev.mysql.com/doc/refman/8.0/en/keywords.html

You can rename them as from_datetime and to_datetime

edited Sep 15 '19 at 17:10

Madhur Bhaiya

28,155
10
49
57

answered Sep 15 '19 at 17:01

Kayla Fuchs

273
1
6

Didn't even occur to me, but it's totally logical, sorry for not realizing this myself. Thank you! – inifus Sep 15 '19 at 18:42
Happens to the best of us! – Kayla Fuchs Sep 15 '19 at 18:51

Cannot insert or update a datetime column with 'yyyy-MM-dd hh:mm:ss' format

1 Answers1