mySQL Date format part 2
Hello Experts
I have inherited some code that no longer works following a re-install of mySQL (a more recent version than the code was originally used for).
The issue seems to be surrounding the format of a date field that I am importing from a text file.
The code is:
LOAD DATA INFILE 'C:/File.txt' INTO TABLE tbl_tmp
FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' IGNORE 1 LINES
(PROCESSED_MONTH, TRAFFIC_MONTH, NETWORK_OPERATOR);
The data is this:
Processed BLP Start Date BLP Start Date BNOP Name
01/07/2012 00:00:00 01/05/2012 00:00:00 BT
01/07/2012 00:00:00 01/06/2012 00:00:00 O2
01/07/2012 00:00:00 01/06/2012 00:00:00 O2
The error is this:
Error Code: 1292. Incorrect datetime value: '01/07/2012 00:00:00' for column 'PROCESSED_MONTH'
How do I alter the column structure on the database such that it accepts a date in this format?
I have inherited some code that no longer works following a re-install of mySQL (a more recent version than the code was originally used for).
The issue seems to be surrounding the format of a date field that I am importing from a text file.
The code is:
LOAD DATA INFILE 'C:/File.txt' INTO TABLE tbl_tmp
FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' IGNORE 1 LINES
(PROCESSED_MONTH, TRAFFIC_MONTH, NETWORK_OPERATOR);
The data is this:
Processed BLP Start Date BLP Start Date BNOP Name
01/07/2012 00:00:00 01/05/2012 00:00:00 BT
01/07/2012 00:00:00 01/06/2012 00:00:00 O2
01/07/2012 00:00:00 01/06/2012 00:00:00 O2
The error is this:
Error Code: 1292. Incorrect datetime value: '01/07/2012 00:00:00' for column 'PROCESSED_MONTH'
How do I alter the column structure on the database such that it accepts a date in this format?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I think I have an extra ";" - just retesting...
ASKER
Right thats working now thank you. But only if I use the SET command to change the PROCESSED_DATE. How do I do the TRAFFIC_DATE? I think I need to do it at the same time as the PROCESED_DATE but cant find the syntax in the docs to do multiple SETS!
ASKER
SOrted - I was using the correct syntax, but had applied a mask at table level. When I removed that all was well.
There are now only 64 warnings to plough through (from half a million rows so not too bad!) thanks for you help
There are now only 64 warnings to plough through (from half a million rows so not too bad!) thanks for you help
ASKER
Thank you
Glad to help and to see you were able to figure out the rest - was already asleep when you replied.
ASKER