I'm loading data using LOAD DATA INFILE in 5.5 where the value includes microseconds. I must use DATETIME data type. Currently, I'm using regular expressions to remove them from the CSV prior to import. I'm wondering if there's an option in MySQL to truncate them so it only loads YYYY-MM-DD HH:MM:SS?


  • Which version of MySQL? Up until 5.5, DATETIME columns store only second precision. – ypercubeᵀᴹ May 12 '14 at 9:03
  • 5.5. Hence needing to remove it before I load. - I've updated the question to reflect it. – user2732180 May 12 '14 at 9:06
  • I don't think you do (need that.) Just try to load a small file first and check the resulting table. It should have no fractional seconds in the datetime values. – ypercubeᵀᴹ May 12 '14 at 9:15

You can use variables to edit your input data for example.

LOAD DATA LOCAL INFILE 'file name ' INTO TABLE table (@var1) SET Date=str_to_date(@var1,'%Y-%m-%d')

You can use this method for a lot of different stuff like loading only the 3 first char of a string or doing some mathematical funcion etc.


LOAD DATA LOCAL INFILE 'file name' INTO TABLE table (@var1) SET Date=substring(@var1,1,3).

Hope this helps

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.