CODEWITHSUNDEEP
mysqldate
Datacrawler
Datacrawler

Reputation: 2876

How to convert Date on MySQL (variable error)

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.

Upvotes: 1

Views: 391

Answers (3)

Deepu Sasidharan
Deepu Sasidharan

Reputation: 5309

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 ..

Upvotes: 1

Abhishek Sharma
Abhishek Sharma

Reputation: 300

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.

Upvotes: 0

Chris Lear
Chris Lear

Reputation: 6742

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.

Upvotes: 0

Related Questions