Convert dd-M-yyyy to mySQL datetime

Question

I have a MySQL varchar column full with dates stored in dd-M-yyyy format. E.g:

Row 1: 12-jan-2010
Row 2: 23-jun-2016

What's the best way to convert this to mySQL datetime format using php?

Answer 1

First, make sure your timezone is UTC: see date_default_timezone_set().

Use strtotime() to convert the existing string to a UNIX timestamp.

Use date() to convert it back to one or another standard format, e.g. Y-m-d H:i:s.

Put it back into MySQL.

Answer 2

Can be done without using php:

update myTable set newcol = STR_TO_DATE(oldcol, '%d-%M-%Y')