How to fix error “You have an error in your syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near… ”?

This kind of errors arise when you have used incorrect syntax. Let us see an example wherein we have created a table and the same error “1054” arise.

Here’s the table −

mysql> create table DemoTable689(
   UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   UserName varchar(100),
   UserLoginDate date(100) NOT NULL
);

This will produce the following output i.e. an error for incorrect syntax usage −

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 '(100) NOT NULL
)' at line 5

Let us now fix the error 1054. For that, you need to use only DATE NOT NULL. Let us first create a table −

mysql> create table DemoTable689(
   UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   UserName varchar(100),
   UserLoginDate date NOT NULL
);
Query OK, 0 rows affected (0.68 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable689(UserName,UserLoginDate) values('John',DATE(NOW()));
Query OK, 1 row affected (0.40 sec)
mysql> insert into DemoTable689(UserName,UserLoginDate) values('Chris','2018-01-21');
Query OK, 1 row affected (0.21 sec)
mysql> insert into DemoTable689(UserName,UserLoginDate) values('Robert',CURDATE());
Query OK, 1 row affected (0.20 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable689;

This will produce the following output. Now, we have fixed the error −

+--------+----------+---------------+
| UserId | UserName | UserLoginDate |
+--------+----------+---------------+
|      1 | John     | 2019-07-21    |
|      2 | Chris    | 2018-01-21    |
|      3 | Robert   | 2019-07-21    |
+--------+----------+---------------+
3 rows in set (0.00 sec)