convert date string to mysql datetime field

Question

I have a bunch of records with dates formatted as a string such as '04/17/2009'

I want to convert them to a mysql datetime field

I plan to use a foreach loop to read the old date value and insert the newly formatted value into a new field in each record

what would be the best way to convert that string...I thought php might have a way to do it automatically?

thanks

If these strings are currently in the db, you can skip php by using mysql's STR_TO_DATE() function.

I assume the strings use a format like month/day/year where month and day are always 2 digits, and year is 4 digits.

UPDATE some_table
   SET new_column = STR_TO_DATE(old_column, '%m/%d/%Y')

You can support other date formats by using other format specifiers.

View more on Stack Overflow

Licensed under: CC-BY-SA with attribution

Not affiliated with: Stack Overflow

Pekka 웃 · Accepted Answer · 2/21/2013 11:22:10 AM

First, convert the string into a timestamp:

$timestamp = strtotime($string);

Then do a

date("Y-m-d H:i:s", $timestamp);

Pekka 웃