Hi Campbell, I tried this LOAD DATA LOCAL INFILE 'abc.txt' INTO TABLE abc FIELDS TERMINATED BY ',' LINES TERMINATED BY '^V\n' (date_format(doj,'%d-%M-%Y %H:%i:%S');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'date_format(doj,'%d-%M-%Y %H:%i:%S')' at line 1 and also LOAD DATA LOCAL INFILE 'abc.txt' INTO TABLE abc FIELDS TERMINATED BY ',' LINES TERMINATED BY '^V\n' (str_to_date(doj,'%d-%b-%Y %H:%i:%S'); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'str_to_date(doj,'%d-%b-%Y %H:%i:%S')' at line 1 Can u please tell me where i going wrong. regards anandkl On 7/11/07, Rhys Campbell <[EMAIL PROTECTED]> wrote:
Can you not change your proceedure and format your dates first using DAT_FORMAT()? http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function _date-format You could put a trigger on the table that would format the dates before insert (although I'd go for the above) -----Original Message----- From: Ananda Kumar [mailto:[EMAIL PROTECTED] Sent: 11 July 2007 06:30 To: MySQL General Subject: Fwd: load data Hi All, We have an application where we load data on a daily basis and then do some analysis and the move this data into different tables. Data is comming in files. The date format in the file is "dd-mon-yyyy hh24:mi:ss', but as you all know, in mysql , the default date format is "yyyy-mm-dd hh24:mi:ss". How can i specifiy this format in the load data infile script. I tried this LOAD DATA LOCAL INFILE 'abc.txt' INTO TABLE abc FIELDS TERMINATED BY ',' LINES TERMINATED BY '^V\n' (doj timestamp "dd-mon-yyyy hh24:mi:ss"); but data is not getting inserted. Please help me. regards anandkl This email is confidential and may also be privileged. If you are not the intended recipient please notify us immediately by telephoning +44 (0)20 7452 5300 or email [EMAIL PROTECTED] You should not copy it or use it for any purpose nor disclose its contents to any other person. Touch Local cannot accept liability for statements made which are clearly the sender's own and are not made on behalf of the firm. Touch Local Limited Registered Number: 2885607 VAT Number: GB896112114 Cardinal Tower, 12 Farringdon Road, London EC1M 3NN +44 (0)20 7452 5300