MySQL: Changing Date Format
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!
Comments are closed.
PO Box 113, Beaconsfield Upper, Victoria, Australia, 3809.