MySQL Forums :: Newbie :: Answered: Need to Convert Date Time at Query runtime


Advanced Search

Answered: Need to Convert Date Time at Query runtime
Posted by: Chris Rife ()
Date: August 05, 2010 09:32PM

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.

Options: ReplyQuote


Subject Written By Posted
Answered: Need to Convert Date Time at Query runtime Chris Rife 08/05/2010 09:32PM
Re: Need to Convert Date Time at Query runtime Chris Rife 08/05/2010 10:11PM
Re: Edit: Need to Convert Date Time at Query runtime Shawn Taylor 08/06/2010 01:48PM
Re: Edit: Need to Convert Date Time at Query runtime Chris Rife 08/06/2010 05:41PM
Re: 1 other thing to correct this issue Chris Rife 08/10/2010 09:16PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.