Date is inserting as 0000-00-00 00:00:00 in mysql

Question

My $date output is in the foreach loop

09/25/11, 02/13/11, 09/15/10, 06/11/10, 04/13/10, 04/13/10, 04/13/10, 09/24/09, 02/19/09, 12/21/08

My mysql query(PHP) is as follows

("INSERT INTO table_name(`field1`, `field2`,`date`) VALUES ('".$value1."','".$value2 ."','".$date."')");

Question: In my database all the dates stores as 0000-00-00 00:00:00. But 4th date (06/11/10) is stored as 2006-11-10 00:00:00.

I tried with date('Y-m-d H:i:s', $date); but no help.

Note: My database field is datetime type. Any idea?

Answer 1

You're on the right track with your date('Y-m-d H:i:s',$date); solution, but the date() function takes a timestamp as its second argument, not a date.

I'm assuming your examples are in American date format, as they look that way. You can do this, and it should get you the values you're looking for:

date('Y-m-d H:i:s', strtotime($date));

The reason it's not working is because it expects the date in the YYYY-MM-DD format, and tries to evaluate your data as that. But you have MM/DD/YY, which confuses it. The 06/11/10 example is the only one that can be interpreted as a valid YYYY-MM-DD date out of your examples, but PHP thinks you mean 06 as the year, 11 as the month, and 10 as the day.