CODEWITHSUNDEEP
phpmysqldate
user264450
user264450

Reputation:

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 all 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?

Upvotes: 0

Views: 1315

Answers (2)

Álvaro González
Álvaro González

Reputation: 146460

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';

Upvotes: 0

Catfish
Catfish

Reputation: 19294

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.

Upvotes: 1

Related Questions