0

I have created an SQL table with a DATETIMES parameter of data type datetime. I am reading a number of rows from a csv file and I am trying to reformat the date time (row[3]), from format %Y%m%d%H%M%S to the datetime compatible %Y-%m-%d %H:%M:%S. Print d2 yields a correct modified date format i.e. 2013-12-13 17:32:12, however using the SQL insert statement below triggers 

Warning: Out of range value for column 'DATETIMES' at row 1

Would anyone have an idea on why a datetime format of this type might be rejected?

for row in csv_data:
    if len(row)>=17:
        indate = row[3]
        d=datetime.strptime(indate, '%Y%m%d%H%M%S')
        d2=d.strftime("%Y-%m-%d %H:%M:%S")
        parsed = (d2, int(row[2]),int(row[4]),int(row[5]),int(row[6]),int(row[7]),int(row[8]),int(row[9]),int(row[10]),int(row[11]),int(row[12]),int(row[13]),int(row[14]),int(row[15]),int(row[16]))
        cursor.execute("""INSERT INTO PR(DATETIMES,PERIOD ,DATA1,DATA2,DATA3, DATA4,DATA5, DATA6, DATA7, DATA8, DATA9, DATA10,DATA11, DAT12, DATA13) \
                VALUES("%s", %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) \
                    ON DUPLICATE KEY UPDATE DATETIMES=values(DATETIMES) ,PERIOD=values(PERIOD),DATA1=values(DATA1) ,DATA2=values(DATA2) ,DATA3=values(DATA3),DATA4=values(DATA4) ,DATA5=values(DATA5) ,DATA6=values(DATA6) ,DATA7=values(DATA7) ,DATA8=values(DATA8) ,DATA9=values(DATA9) ,DATA10=values(DATA10) ,DATA11=values(DATA11) ,DATA12=values(DATA12) ,DATA13=values(DATA13);""", parsed)
mydb.commit()
afuzzyllama
  • 6,538
  • 5
  • 47
  • 64
user3080213
  • 39
  • 1
  • 2
  • 10
  • Are you on sql server? – Mihai Dec 23 '13 at 19:47
  • http://stackoverflow.com/questions/2420728/how-to-prevent-out-of-range-datetime-value-error – Mihai Dec 23 '13 at 19:49
  • 1
    I suggest inserting your csv data into a staging table first, with varchar fields. Then use your database to cast the strings to datetimes. – Dan Bracuk Dec 23 '13 at 19:58
  • Currently using MySQLdb. I am not familiar with casting but will try to find out more details. – user3080213 Dec 23 '13 at 20:07
  • I encountered the same issue with `MySQLdb`. Check this post http://stackoverflow.com/questions/15420470/mysqldb-converts-timestamp-data-to-none – ronak Dec 23 '13 at 20:25
  • I forgot to add that the records (all but the datetime d2 in code above), are correctly assigned for the first row in the table. In the DATETIMES field the record reads as 'None' – user3080213 Dec 23 '13 at 20:33
  • Echo of Dan Bracuk mostly...Do not import into a date time field, import into a staging table with this defined as a varchar field. Create the final table with the proper DateTime field in it and do the convert to date time there. I've had to do this for pretty much every text file with dates I've imported. – Twelfth Dec 23 '13 at 21:12

0 Answers0