MySQL Forums
Forum List  »  General

datetime warning
Posted by: Peter Kanas
Date: April 09, 2013 06:47PM

I don't see what's wrong with the datetime format in the warning below? This record ends up in the table with the event_date set to 2013-03-10 03:00:00. I see this value 282 times which is consistent with the number of warnings coming from mysqlimport - see below.

Each record has as 5 datetime columns. All datetime values are formated using the same awk script. All but the 282 values are loaded correctly. Many have a one digit hour incoming and end up with leading zero for hour and load ok.

I put the awk function that generates the datetime string below and am guessing the hour character are not as expected given the script goes between char and int. Awk usually does the right thing in these cases...
============================================================================
mysql> LOAD DATA INFILE 'c:/$programs/mysql/defect_data_cache/metadata' INTO TABLE metadata FIELDS TERMINATED BY '\t'; S
HOW WARNINGS\G;
ERROR 1292 (22007): Incorrect datetime value: '2013-03-10 02:35:47' for column 'EVENT_DATE' at row 160703
*************************** 1. row ***************************
Level: Error
Code: 1292
Message: Incorrect datetime value: '2013-03-10 02:35:47' for column 'EVENT_DATE' at row 160703
1 row in set (0.00 sec)

==========================================================================
$mysqlimport -L -i grenada metadata
grenada.metadata: Records: 213572 Deleted: 0 Skipped: 0 Warnings: 282
==========================================================================
# string passed to function=3/10/2013 3:35:47 AM

function mysql_date(string) # convert date to mysql format
{ # input format example: 2/18/2013 8:44:06 PM
split(string,a," ")
split(a[2],time,":")
h=time[1]
if (index(a[3],"PM")==1){
h=time[1]+11
}else{
if (time[1]==12){
h=0
}else{
h=time[1]
}
}
split(a[1],date,"/")
return(sprintf("%04d-%02d-%02d %02d:%02d:%02d",date[3],date[1],date[2],h,time[2],time[3]))
}



Edited 2 time(s). Last edit at 04/10/2013 06:22PM by Peter Kanas.

Options: ReplyQuote


Subject
Written By
Posted
datetime warning
April 09, 2013 06:47PM
April 10, 2013 09:09PM
April 16, 2013 10:28AM
April 17, 2013 07:32AM


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.