php 日期在 mysql 中插入为 0000-00-00 00:00:00
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17077071/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Date is inserting as 0000-00-00 00:00:00 in mysql
提问by vidhyakrishnan
My $dateoutput is in the foreach loop
我的$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
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
我的mysql查询（PHP）如下
("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.
问题：在我的数据库中，所有日期都存储为0000-00-00 00:00:00. 但是第四个日期 (06/11/10) 存储为2006-11-10 00:00:00.
I tried with date('Y-m-d H:i:s', $date);but no help.
我试过date('Y-m-d H:i:s', $date);但没有帮助。
Note: My database field is datetime type. Any idea?
注意：我的数据库字段是日期时间类型。任何的想法？
回答by Joel Hinz
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.
您的date('Y-m-d H:i:s',$date);解决方案走在正确的轨道上，但 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.
它不工作的原因是因为它需要 YYYY-MM-DD 格式的日期，并尝试以此评估您的数据。但是你有 MM/DD/YY，这会混淆它。06/11/10 示例是您示例中唯一可以解释为有效 YYYY-MM-DD 日期的示例，但 PHP 认为您的意思是 06 是年，11 是月，10 是日。
回答by vikingmaster
I created my own function for this purpose, may be helpful to you:
我为此创建了自己的函数，可能对您有所帮助：
function getTimeForMysql($fromDate, $format = "d.m.y", $hms = null){
if (!is_string($fromDate))
return null ;
try {
$DT = DateTime::createFromFormat($format, trim($fromDate)) ;
} catch (Exception $e) { return null ;}
if ($DT instanceof DateTime){
if (is_array($hms) && count($hms)===3)
$DT->setTime($hms[0],$hms[1],$hms[2]) ;
return ($MySqlTime = $DT->format("Y-m-d H:i:s")) ? $MySqlTime : null ;
}
return null ;
}
So in your case, you use format m/d/yy:
所以在你的情况下，你使用 format m/d/yy：
$sql_date = getTimeForMysql($date, "m/d/yy") ;
if ($sql_date){
//Ok, proceed your date is correct, string is returned.
}
回答by user4035
You don't have the century in your date, try to convert it like this:
您的日期中没有世纪，请尝试将其转换为：
<?php
$date = '09/25/11';
$date = DateTime::createFromFormat('m/d/y', $date);
$date = $date->format('Y-m-d');
print $date;
Prints:
印刷：
2011-09-25
Now you can insert $date into MySQL.
现在您可以将 $date 插入 MySQL。
