mysql error on query (add_date error) using php

Question

I have this mysql statement and it's giving me error:

INSERT INTO envios ( fecha_estimada_entrega) VALUES (ADDDATE('15/10/2011', INTERVAL 8    DAY));

When i run it i get this meesage:

#1048 - Column 'fecha_estimada_entrega' cannot be null

Can you please help me out on this?

I'm sure it's quite simple, but my head is burned.

Thanks.

mu is too short · Accepted Answer · 2011-10-16 00:42:03Z

2

Try changing your date format to the standard ISO 8601 format:

INSERT INTO envios (fecha_estimada_entrega)
VALUES (ADDDATE('2011-10-15', INTERVAL 8 DAY));

MySQL is probably trying to parse 15 as a month number, failing, and converting your '15/10/2011' date to NULL. Switching to ISO 8601 (the one true date format BTW) gets me sensible results:

mysql> select ADDDATE('2011-10-15', INTERVAL 8 DAY);
+---------------------------------------+
| ADDDATE('2011-10-15', INTERVAL 8 DAY) |
+---------------------------------------+
| 2011-10-23                            |
+---------------------------------------+

As an aside, you should always use ISO 8601 date and time formats internally and limit other formats to the edges (i.e. user input and final display). You'll save yourself a lot of trouble that way and they'll even sort properly as strings.

edited Oct 16, 2011 at 0:42

answered Oct 16, 2011 at 0:36

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gustavo Benito Silva Over a year ago

That solved it thanks. I guess i still have to learn about date formatting.

Jon Gauthier · Accepted Answer · 2011-10-16 00:40:06Z

1

You need to provide a date format which MySQL understands (ex. Y-M-D).

INSERT INTO envios ( fecha_estimada_entrega )
VALUES ( ADDDATE('2011-10-15', INTERVAL 8 DAY) );

edited Oct 16, 2011 at 0:40

answered Oct 16, 2011 at 0:34

Jon Gauthier

25.7k6 gold badges66 silver badges69 bronze badges

1 Comment

Jon Gauthier Over a year ago

Sorry, didn't know about that alternative.. I've always used DATE_ADD. Updated to match the question.

Collectives™ on Stack Overflow

mysql error on query (add_date error) using php

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related