Mysql convert 'CYYMM' to 'YYMM'

Question

Suck with the following:

$loaddata = "LOAD DATA INFILE 'filename.csv' 
    INTO TABLE tb1
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\\r\\n'
    IGNORE 1 LINES
    (
    Entity,
    HK,
    @Period,
    )
    SET Period = STR_TO_DATE(@Period,'%C%YY%MM')
    ";

which gives me and sql syntax error near

 ) SET Period = STR_TO_DATE(@Period,'%C%YY%MM')

Period is a DATE variable. for the period Oct-13 the cvs will show 11310.

tks in advance!

Answer 1

You have a superfluous comma after @Period:

$loaddata = "LOAD DATA INFILE 'filename.csv' 
    INTO TABLE tb1
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\\r\\n'
    IGNORE 1 LINES
    (
    Entity,
    HK,
    @Period -- , removed here
    )
    SET Period = STR_TO_DATE(@Period,'%C%YY%MM')
    ";

However, your date format string is almost certainly incorrect. %C, %YY and %MM are invalid specifiers. See DATE_FORMAT().