2 Replies - 1298 Views - Last Post: 15 October 2012 - 07:48 AM

#1 yaykittyeee  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 59
  • Joined: 16-September 10

strtodate format problem

Posted 14 October 2012 - 06:00 PM

Hello,

I'm having problems converting a string to a date in mysql.

The date looks like this in the file.

'918378978', 15, 89, '01-FEB-2009'
'112298730', 25, 92, '15-MAR-2009'
'913783708', 5, 64, '07-APR-2009'


The command i'm using looks like this, i'm trying to use the str_to_date function to convert the date
LOAD DATA LOCAL INFILE 'file location'
INTO TABLE GRADES
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '\''
LINES TERMINATED BY '\n'
(ssn, Percentage, Score, @var1)
set Submit_Date = STR_TO_DATE(@var1, '%d-%b-%Y');


when i try this command, i get the following warnings and the date is not inserted into the table:
for each row:
|Warning | 1411 | Incorrect datetime value | for function str_to_date | datetime value: ' '07-APR-2009' '

thanks

This post has been edited by Atli: 14 October 2012 - 06:41 PM
Reason for edit:: Use [code] tags when posting code.


Is This A Good Question/Topic? 0
  • +

Replies To: strtodate format problem

#2 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3719
  • View blog
  • Posts: 5,990
  • Joined: 08-June 10

Re: strtodate format problem

Posted 14 October 2012 - 07:03 PM

Hey.

Looks like MySQL is complaining about the single-quote and space characters around the date string. That could be due to the fact that you are using an incorrect FIELDS TERMINATED BY value. You specify it as ',', when in fact you use ', ' to separate the fields in the data you posted.

Try fixing that and see if MySQL can parse the dates correctly then.
Was This Post Helpful? 1
  • +
  • -

#3 yaykittyeee  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 59
  • Joined: 16-September 10

Re: strtodate format problem

Posted 15 October 2012 - 07:48 AM

yes!! thanks a lot. it works perfectly.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1