0

What I want to do is transfer the "12/26/17 14:30" in txt to "2017-12-26 14:30:00 " in MySQL

I've already tried this Importing a CSV to MySQL with different date format0

So my code looks like this 

 create_tb_str = r'''DROP TABLE IF EXISTS TABLENAME;
        CREATE TABLE TABLENAME (
        Datetime DATETIME,
        Open float(8,3),            
        High float(8,3),            
        Low float(8,3),             
        Close float(8,3), 
        Volume INT,                           
        PRIMARY KEY (Datetime)
        );'''
insert_tb_str = r''' LOAD DATA LOCAL INFILE 'FILENAME'  
INTO TABLE  TABLENAME  FIELDS TERMINATED BY '\t\t'  
ENCLOSED BY '"' LINES
TERMINATED BY '\n' 
(@time, open, high, low, close, @dummy, volume);
SET Datetime = STR_TO_DATE(@time,'%y-%m-%d %H:%i:%S');   
'''

However, after I generate the .sql and try to import to my db, this is the result I get, the terminal can not recognize my table's column name.

$:~/Downloads/bartest$ mysql -u root -p bar -A < bartest.sql
Enter password: 
ERROR 1193 (HY000) at line 21: Unknown system variable 'Datetime'

Any suggestion?

Rick James
  • 135,179
  • 13
  • 127
  • 222
Chin-I
  • 1
  • You should connect to a specific database, not just smash around as root and create them in the `mysql` namespace. – tadman Jan 17 '18 at 18:36
  • 1
    You're using a reserved MySQL parameter as a `name` --- `DATETIME` should **not** be used as a `name`. I would rename your field to something less "parameter-ish" – Zak Jan 17 '18 at 18:36

1 Answers1

0

STR_TO_DATE(@time,'%y-%m-%d %H:%i:%S'); is returning null as it's not matching the string input pattern.

Try something like this instead: STR_TO_DATE('12/26/17 14:30','%m/%d/%y %k:%i');

After extracting in DATE, convert it to your desired Date format.

To do in single step, you can also try: "SELECT CONVERT(datetime, '12/26/17 14:30');"

Hope that helps!

shubinj
  • 1
  • 2