Insert MySQL date

[markjia]

I'm having problems inserting rows into a MySQL table that contains a column of type 'Date'.

Here is how the table is created:
$Query = "CREATE TABLE News (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, DateField DATE NOT NULL, NewsField TEXT NOT NULL)";

Here is an example of a query used to add new rows:
INSERT INTO News VALUES ('0', '2002.31.7', 'fda')

(note: using a date of 20023107 does not make a difference)

The date is not added correctly and instead of 2002-31-7, it stores the default (0000-00-00).

[[deleted]]

try changing the format of:

2002.31.7

to match, 2002-31-07, mysql is probably throwing out your date because it is invalid.

taken from http://www.mysql.com/doc/D/A/DATETIME.html

To get a MySQL compatible DATETIME string with php, use the following: $datetime = date("Y-m-d H:i:s");

[siwis]

Alternatively, assuming you want the current date anyway, you should be able to use the mysql now() function within your sql:

INSERT INTO News VALUES ('0', now(), 'fda');

note: now() is NOT enclosed in single quotes because it is a mysql function, NOT php

[markjia]

I have also used "2002-31-07", but it doesn't help. The date is also not necessarily the current date, so I can't use the date() function.

[muadzir]

can you try this instead

$strDate = date("Y-m-d");

you can grab today's date and it is of the MySql date format. It work fine with me.

if it is still does not work, try altering your table definition from this

$Query = "CREATE TABLE News (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, DateField DATE NOT NULL, NewsField TEXT NOT NULL)";

into this

$Query = "CREATE TABLE News (id INT UNSIGNED NOT NULL AUTO_INCREMENT, DateField DATE NOT NULL, NewsField TEXT NOT NULL, PRIMARY KEY(id))";

hope it will work