I have needed to import a significant amount of information into MySQL from spreadsheets, where a date/time entry is in the format of DD/MM/YYYY H:MM (e.g. 01/01/2018 19:00). When I tried to import into MySQL, the field was truncated as the format was not a correct DATETIME value which requires the information to be in the form of YYYY-MM-DD HH:MM.

Thankfully the fix is fairly simple, using the STR_TO_DATE command to allow MySQL to recognise the date format, and then DATE_FORMAT to allow MySQL to present it in a specific way. The format is below;

DATE_FORMAT(STR_TO_DATE(CombinedDateTime, “%d/%m/%Y %H:%i:%s.%f”), “%Y-%m-%d %H:%i:%s”)

The command above is looking in the CombinedDateTime to get the value, which I have told it is in the format of DD/MM/YYYY HH:MM:SS and then converting the date found into the format of YY-MM-DD HH:MM:SS.


Hope this helps!

Prev Remove Public Holidays from MySQL Results
Next MIOPS Mobile Motion Trigger

Comments are closed.

%d bloggers like this: