How to insert date(y-m-d) into MySQL database ?

Question

I have a field of type date in MySQL. When I try to insert any date in this field with PHP using following query, It stores 0000-00-00 in that field.

For example:

UPDATE test SET dob=2000-09-20 WHERE id=3

Answer 1

Just quote the date.

UPDATE test SET dob='2000-09-20' WHERE id=3

In your query ... 2000-09-20 ... will be interpreted as a mathematical expression. The result is a number, 1971. In the date time field, a number will be zerofilled to a 6, 8, 12 or 14-digit number, so 1971 will become 001971. This number is then interpreted as YYMMDD format, so it means "year 00 month 19 day 71", which is invalid. So the special value 0000-00-00 is stored.

Answer 2

Normally, you would use '2000-09-20' as the value to insert. How does your system distinguish between what you have and the integer (non-date) value 1971, which is what you'd get by subtracting 9 and 20 from 2000?

UPDATE test SET dob = '2000-09-20' WHERE id = 3

See here for more details.