datetime warning
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.
Subject
Written By
Posted
datetime warning
April 09, 2013 06:47PM
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.