Properly formatted MySQL date insert statement returns all 0's
I've created a PHP script that parses an HTML page, creates a MySQL query, and inserts the parsed data into a MySQL table. Everything works fine, except for one thing : the date field. When PHP runs the MySQL statement, the dates in the table read 0000-00-00 . If I echo the MySQL query, it results in the following :
INSERT INTO dispatch_table (dispatch_date, dispatch_time, dispatch1, dispatch2, dispatch3, dispatch4, dispatch5, dispatch6, dispatch7, dispatch8, dispatch9, dispatch10, dispatch11, dispatch12, dispatch13, dispatch14, dispatch15, dispatch16)VALUES ('2010-02-02', '10:46:17', '31.90' , '32.15','32.24','32.39','33.46','35.18','39.33','39.39','40.92','41.79','41.82','44.35','45.47','46.89','47.13','67.59');
If I copy and paste the statement into the MySQL table, it inserts the date just fine, but when PHP trys to insert it, I am given al开发者_如何学Cl 0's. The dispatch_date field is a "date" field in MySQL. Before inserting the dates with the PHP code, the date to insert is converted to a string (I thought this might solve my problem). Can anyone give me any insight on this?
From tizag.com
When you enter dates that are out of the range or in the wrong format for a given date type, MySQL will often just enter in the default value of all zeros.
You are storing them in the wrong format.
2010-02-02 (%Y-%m-%d) is the default format but it can be changed. Run this query to learn what the values are in your server:
SELECT @@date_format, @@datetime_format;
If they don't match, you'll have to either change your queries or change the @@date_format and @@datetime_format variables. They can be changed in a per-session basis:
SET @@session.date_format='%Y-%m-%d';
SET @@session.datetime_format='%Y-%m-%d %H:%i:%s';
精彩评论