日期在MySQL中插入为0000-00-00 00:00:00 [英] Date is inserting as 0000-00-00 00:00:00 in mysql

问题描述

我的$date输出在foreach循环中

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

我的mysql查询(PHP)如下

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

问题:在我的数据库中，所有日期都存储为0000-00-00 00:00:00.但是第4个日期(06/11/10)被存储为2006-11-10 00:00:00.

我尝试了date('Y-m-d H:i:s', $date);，但没有帮助.

注意:我的数据库字段是日期时间类型. 有什么主意吗?

解决方案

您在使用date('Y-m-d H:i:s',$date);解决方案的过程中走了正确的路，但是date()函数将时间戳作为第二个参数，而不是日期. /p>

我假设您的示例采用美国日期格式，因为它们看起来像那样.您可以执行此操作，它应该为您提供所需的值:

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

它不起作用的原因是因为它期望使用YYYY-MM-DD格式的日期，并尝试以此方式评估您的数据.但是您有MM/DD/YY，这使它感到困惑.示例中只有06/11/10的示例可以被解释为有效的YYYY-MM-DD日期，但是PHP认为您将06表示为年，将11表示为月，将10表示为日.

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?

解决方案

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.

这篇关于日期在MySQL中插入为0000-00-00 00:00:00的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！