How to convert Date on MySQL (variable error)

Question

I have created this table on MySQL :

create table Apolo(
Date date,
Name varchar(50)
);

I have imported an excel file :

LOAD DATA LOCAL INFILE 'C:/Users/File.csv'
INTO TABLE Apolo
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 10 ROWS
(@Date, Name);
set Date=str_to_date(@Date,'%d/%m/%Y');

and I get the error :

Error Code: 1193. Unknown system variable 'Date'

If I do not put this line :

set Date=str_to_date(@Date,'%d/%m/%Y');

I do not get the error but if I try to use :

select count(*) from Apolo where Date='03/09/2015';

it does not work. So the format is not recognisable.

is your data imported successfully along with date? also i see you create a table name Apolo but when you run query select count(*) from Apolo_BBC here table name is change Apolo_BBC — Noman
– Noman, Commented Sep 30, 2015 at 9:42
@Noman I changed the table name for stackoverflow. In MySQL it is correct. The problem is not this one :( — Datacrawler
– Datacrawler, Commented Sep 30, 2015 at 10:05

Deepu Sasidharan · Accepted Answer · 2015-09-30 11:17:44Z

1

The date to insert into mysql database should be in the format

YYYY-MM-DD

Example: 2015-02-18

So change the date in csv file to the above specified format and try ..

answered Sep 30, 2015 at 11:17

Deepu Sasidharan

5,32911 gold badges46 silver badges100 bronze badges

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

Comments

Abhishek Sharma · Accepted Answer · 2015-09-30 11:10:30Z

0

Check your Date column values in database. By default date format in mysql database is Y-m-d

Do you have values in this format.

answered Sep 30, 2015 at 11:10

Abhishek Sharma

3002 silver badges7 bronze badges

2 Comments

Datacrawler Over a year ago

The date on the .csv file is shown as "03/09/2015".

Abhishek Sharma Over a year ago

You are right on this. But you need to change this to Y-m-d format before insert to database. If you dont want to change format before insert, make sure your date column in database table contain dates.

Chris Lear · Accepted Answer · 2015-09-30 09:48:08Z

0

Take the semi-colon out of this line

(@Date, Name);

The set should be in the same command. Watch out for the fact that you have a field name that is a reserved word - you may need to escape it.

answered Sep 30, 2015 at 9:48

Chris Lear

6,7601 gold badge20 silver badges29 bronze badges

2 Comments

Datacrawler Over a year ago

I did that. The date on the .csv file is shown as "03/09/2015". I deleted the semicolon and then proceeded with this query : select count(*) from Apolo where Date='03/09/2015'; but I get 0 as a result.

Chris Lear Over a year ago

I don't know what your csv looks like, or what is in your table after you import the csv. So I can't really comment any further I'm afraid.

Collectives™ on Stack Overflow

How to convert Date on MySQL (variable error)

3 Answers 3

Comments

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related