将各种格式的日期转换为mysql DATE格式以保存在数据库PHP中

Question

Ok, so I have a bit of a mystery on my hands. I hve a bunch of dates in all different, mostly American, formats. I need to add them to a mysql database in a DATE field. So I wrote the blow code to take whatever divider they had and replace it with a hyphen and then used strtotime in conjunction with the date function to convert it to the right format. I thought I would be home free, but no.

$q = "SELECT date_rendered, date_requested, record_id FROM client_svc_history";
$r = mysql_query($q) or die(mysql_error());

while($row = mysql_fetch_assoc($r)) {
    $record_id = $row['record_id'];
    echo $record_id.": ".$row['date_rendered']."  ";
    $date_rendered = preg_replace('~[^0-9]~','-',$row['date_rendered']);
    echo $date_rendered." ";
    $date_rendered = date('Y-m-d',strtotime($date_rendered));
    echo $date_rendered."<br> ";
    echo $record_id.": ".$row['date_requested']." ";
    $date_requested = preg_replace('~[^0-9]~','-',$row['date_requested']);
    echo $date_requested." ";
    $date_requested = date('Y-m-d',strtotime($date_requested));
    echo $date_requested."<br>";
}

I echo'ed out the data so I could make sure it was working properly before I used it to write back to the database.

Below is a sampling of the output I got. Notice that in record 7 it took 03-23-2006 and zero'ed it out. In record 8 it handled the same format with no problem, down in records 11, 12 & 13 it zero'ed out 2 more formats that I thought strtotime could handle. I am at a loss. I appreciate any help. Thanks.

1: 0000-00-00 0000-00-00 1969-12-31
1: 2005-02-25 2005-02-25 2005-02-25
2: 0000-00-00 0000-00-00 1969-12-31
2: 0000-00-00 0000-00-00 1969-12-31
3: 2005-03-08 2005-03-08 2005-03-08
3: 2005-03-08 2005-03-08 2005-03-08
4: 2005-03-08 2005-03-08 2005-03-08
4: 2005-03-08 2005-03-08 2005-03-08
5: 2005-03-08 2005-03-08 2005-03-08
5: 2005-03-08 2005-03-08 2005-03-08
6: 2005-03-08 2005-03-08 2005-03-08
6: 2005-03-08 2005-03-08 2005-03-08
7: 03-23-2006 03-23-2006 1969-12-31
7: 03-23-2006 03-23-2006 1969-12-31
8: 04-10-2006 04-10-2006 2006-10-04
8: 04-10-2006 04-10-2006 2006-10-04
9: 04-10-2006 04-10-2006 2006-10-04
9: 04-11-2006 04-11-2006 2006-11-04
10: 2/10/2006 2-10-2006 2006-10-02
10: 02-10-2006 02-10-2006 2006-10-02
11: 11/29/2005 11-29-2005 1969-12-31
11: 11-29-2005 11-29-2005 1969-12-31
12: 11/29/2005 11-29-2005 1969-12-31
12: 11-29-2005 11-29-2005 1969-12-31
13: 01-26-06 01-26-06 1969-12-31
13: 01-26-2006 01-26-2006 1969-12-31

Answer 1

strtotime() won't be able to render accurately string dates that contain - in them, because it will try to deduct the time from the current timestamp (e.g. -1 day...). Try replacing hyphens - with a slash /.