using php, how would I convert all rows of a char field such as "11/19/10" to a date field?

Question

In order to import a column of dates into mysql using phpmyadmin, I defined the field as a char field.

So now the dates are in a mysql table look like this:

ID | Name | DateArriving
1  | bill | 11/19/10
2  | tom  | 12/1/10

etc....

I want the DateArriving field to be formated as actual dates so I can do certain queries such as "who is arriving within the next week".

I would like guidance on how the PHP code should look to do this conversion.

Thank you.

ADDENDUM FINAL SOLUTION I USED:

In the end, I used a combination of a few answers:

$select_trips = "SELECT * FROM trips WHERE STR_TO_DATE(DateArriving, '%m/%d/%Y') BETWEEN NOW() AND NOW() + INTERVAL 1 WEEK"; 

Answer 1

using strtotime you can convert your date to time

http://php.net/manual/en/function.strtotime.php

then pass that time to date function and format it as you want

 date('l F Y h:i:s A', $timestamp);

See the examples in the above link

use date_diffor diff for comparing

Answer 2

If you would use a DATETIME column type for the dates, you could do

SELECT Name, DateArriving
  FROM Arrivals 
 WHERE DateArriving BETWEEN NOW() AND NOW() + INTERVAL 2 WEEK;

and wouldnt have to do any conversions in PHP at all.

Using a DATETIME column would also allow you to pass different date formats for query params, so you are not tied to a specific format when executing statements against the column, e.g.

// create a new DB connection and prepare statement
$db = new mysqli('127.0.0.1', 'foo', 'secret', 'dbname');
$stmt = $db->prepare(
    'SELECT Name FROM Arrivals WHERE DateArriving BETWEEN ? AND ?');

// bind the query params in two different time formats
$startDate = date('Y/m/d');
$endDate   = date('Y-m-d', strtotime('+2 weeks'));
$stmt->bind_param('ss', $startDate, $endDate);

// execute the query and fetch the first result
$stmt->execute();
$stmt->bind_result($result);
$stmt->fetch();
printf ("%s\n", $result);
$stmt->close();

See the DateTime functions in MySQL.

Answer 3

Now, I'm not a big-city SQL guru, but I'd go about it like this:

1. create a new table with the structure you really want

2. read the data from the existing table row by row, converting the date to a format MySQL can understand, then inserting it into the new table

3. drop the original table

4. rename the new table to whatever your existing code expects

5. thank $DEITY that the text dates are in a single consistent format rather than a user-supplied mishmash of incompatible regional variations (like d/m/yyyy mixed with m/d/yyyy)

Answer 4

SELECT STR_TO_DATE(DateArriving, '%m/%d/%Y') FROM TABLE

Answer 5

$select_trips = "SELECT * FROM trips 
              WHERE STR_TO_DATE(DateDeparting, '%m/%d/%Y') 
              BETWEEN NOW() AND NOW() + INTERVAL 1 WEEK