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

Answer 1

$time = strtotime($oldtime);

Then use date() to put it into the correct format.

Answer 2

First, convert the string into a timestamp:

$timestamp = strtotime($string);

Then do a

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

Answer 3

If these strings are currently in the db, you can skip php.

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

Answer 4

I assume we are talking about doing this in Bash?

I like to use sed to load the date values into an array so I can break down each field and do whatever I want with it. The following example assumes and input format of mm/dd/yyyy...

DATE=$2
DATE_ARRAY=(`echo $DATE | sed -e 's/\// /g'`)
MONTH=(`echo ${DATE_ARRAY[0]}`)
DAY=(`echo ${DATE_ARRAY[1]}`)
YEAR=(`echo ${DATE_ARRAY[2]}`)
LOAD_DATE=$YEAR$MONTH$DAY

you also may want to read up on the date command in linux. It can be very useful: http://unixhelp.ed.ac.uk/CGI/man-cgi?date

Hope that helps... :)

-Ryan