PHP Insert SQL date

Question

I have this code:

$date = '1991-08-13';

$sql =  "INSERT into TABLEA
        (nombre, apellido1, apellido2, direccion, codigoPostal,fechaNacimiento, notas) 
        VALUES 
        ('agds', 'asdgff', 'gerth', 'dfghdfghd efdgvwr', 86486, $date, 'ShhhHH');";

My problem is the next one:

When I insert that $date's value is: 1991-13-08 but in the DB appears: 0000-00-00 I've seen other posts with solutions like: STR_TO_DATE() and it dosen't work for me, it inserts NULL. Any other solution?

EDIT: missing: '' in $date (it should have been: '$date') Thanks for the correction! (:

That date is in the wrong format. Mysql dates are `yyyy-mm-dd`. If it is a date column. — chris85, Nov 05 '15 at 12:26
Possible duplicate of [MySQL str\_to\_date produces NULL despite valid formatting](http://stackoverflow.com/questions/20356813/mysql-str-to-date-produces-null-despite-valid-formatting) — StarShine, Nov 05 '15 at 13:15

score 5 · Accepted Answer · answered Nov 05 '15 at 12:24

5

Please enclose the $date with single quotes like

$sql = "INSERT into TABLEA (nombre, apellido1, apellido2, direccion, codigoPostal,fechaNacimiento, notas) VALUES ('agds', 'asdgff', 'gerth', 'dfghdfghd efdgvwr', 86486, '$date', 'ShhhHH');";

answered Nov 05 '15 at 12:24

Arun Krish

2,153
1
10
15

4

you will also need to convert the date in `yyyy-mm-dd` format – Suyog Nov 05 '15 at 12:27
The date wasnt in that format, i just typed it wrong. It was exactly what @Arun Krish said. – Roucher Nov 05 '15 at 13:38
@Roucher if Arun's answer is correct than I think you selected the wrong answer as the accepted answer. – chris85 Nov 05 '15 at 14:49
Both are correct, because both mark the error: '$date' the main reason that I choosed the other one was the time. Imran posted it before, But it's true Arun explained it better – Roucher Nov 06 '15 at 08:13

score 0 · Answer 2 · answered Nov 05 '15 at 12:30

Maybe you can try by this way:

$date = '1991-08-13'; //yyyy-mm-dd

$sql =  "INSERT into TABLEA
        (nombre, apellido1, apellido2, direccion, codigoPostal,fechaNacimiento, notas) 
        VALUES 
        ('agds', 'asdgff', 'gerth', 'dfghdfghd efdgvwr', 86486, '$date', 'ShhhHH');";

Abhishek Sharma · Answer 3 · 2015-11-05T13:05:54.940

0

use Str_to_date

Str_to_date('1991-13-08','%Y-%d-%m')

or try this :-

$sql = "INSERT into TABLEA (nombre, apellido1, apellido2, 
direccion, codigoPostal,fechaNacimiento, notas) 
VALUES ('agds', 'asdgff', 'gerth', 'dfghdfghd efdgvwr',
86486, Str_to_date("1991-13-08","%Y-%d-%m"), 'ShhhHH');";

edited Nov 05 '15 at 13:05

answered Nov 05 '15 at 12:43

Abhishek Sharma

6,689
1
14
20

"I've seen other posts with solutions like: STR_TO_DATE() and it dosen't work for me, it inserts NULL" – Roucher Nov 05 '15 at 13:39
$sql = "INSERT into TABLEA (nombre, apellido1, apellido2, direccion, codigoPostal,fechaNacimiento, notas) VALUES ('agds', 'asdgff', 'gerth', 'dfghdfghd efdgvwr', 86486, Str_to_date("1991-13-08","%Y-%d-%m"), 'ShhhHH');"; – Abhishek Sharma Nov 05 '15 at 13:42
try this query @Roucher – Abhishek Sharma Nov 05 '15 at 13:42
Returns NULL, I can't comprehend why you focus on saying "str_to_date" if i had already said twice that wasnt that and said the correction, thanks for the attempts anyways – Roucher Nov 05 '15 at 13:44
I think i might not be understanding you, but data format (not mine) is specified here: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-types.html – Roucher Nov 05 '15 at 14:29

PHP Insert SQL date

3 Answers3