Answered: Need to Convert Date Time at Query runtime

[Chris Rife]

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.