Edit (8/6 6:45 PM CST):
Answered nicely by Shawn Taylor. Thanks
edit/Update (8/6 8:05 AM CST):
I discovered the Str_to_date() function. I would still love to know if that could be used in the Load Data Infile statement, but for my current application, I have to import the data into a temp table and show it to the user to confirm the data is correct because it is coming from an experimental new machine out on the lumber yard.
Since I am loading a temp table anyway, I will just perform the Str_to_Date() after the records have been validated and are being inserted into the final table.
If anyone cares to shed more light on it though, I would love to learn more about this.
Thanks for reading.
Chris
---------------------- Original Post -----------------------
Good evening,
Hope its cooler where you are than it is here.
I have been trying to figure out how to convert a date time like
1/1/2010 12:00:00 AM
to the mySql
2010-01-01 00:00:00
I need to do this as a 'SET' value in a LOAD DATA INFILE.
What I am working with now is a series of plain text files like this:
C:\TEST\SCANNER\Setup\5015684.set
2
5015684
8/3/2010 7:04:44 AM
Poplar
Beavers
279454
FAS
1
the table I am inserting each file into is:
+-------------+------------------------------
---------------------------------------------
| setfiletest | CREATE TABLE `setfiletest` (
`ticknum` varchar(20) NOT NULL,
`sDate` datetime DEFAULT NULL,
`specieLong` varchar(45) DEFAULT NULL,
`custLong` varchar(45) DEFAULT NULL,
`workOrder` int(11) DEFAULT NULL,
`gradeAbrv` varchar(45) DEFAULT NULL,
`thickness` double DEFAULT NULL,
PRIMARY KEY (`ticknum`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------------+------------------------------
---------------------------------------------
1 row in set (0.00 sec)
I am skipping the first row, but need the rest. This is my Load Data Statement
mysql> Load data infile 'C:/TEST/SCANNER/SETUP/5015684.set'
-> into table setfiletest
-> fields terminated by '\r\n' ignore 1 lines
-> ;
ERROR 1292 (22007): Incorrect datetime value: '8/3/2010 7:04:44 AM' for column 'sDate' at row 1
I know I can manually assign which 'columns' (or in this case rows) are inserted into which Table column..
Is there a function to Convert the MS standard datetime to MySQL standard in the Load Statement?
Thanks for any assistance/ direction in advance
Chris
Edited 2 time(s). Last edit at 08/06/2010 05:46PM by Chris Rife.