KP Joy KP Joy - 2 years ago 104
SQL Question

String to Date conversion for format DD/MM/YYYY in PHP and MYSQL is not working

I am having problem while inserting the date into the MYSQL database. Consider following variable:

$mydate = '18/06/2016'
// dd/mm/yyyy format

Now, I am trying to insert this date into the date field(dob) of my MYSQL database which is throwing me syntax error. Following is my PHP/MYSQL query:

insert into mytable (dob) values (date('dd/mm/yyyy', strtotime($mydate)))

Earlier, when I used to accept date format in mm/dd/yyyy, it was easily getting inserted without any conversion, like this:

insert into mytable (dob) values ($mydate)

What I am missing here?


My actual query is:

insert into users(fullname, dob, notes) values ('Joy', date('Y-m-d', strtotime('15/06/2016')), 'My notes');

Syntax error is:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' strtotime('15/06/2016')), 'My notes')' at line 1

Answer Source

That's probably because MySQL accepts YYYY-MM-DD format. Try to insert like this,

     $date = DateTime::createFromFormat('d/m/Y', $mydate);
     $sql="INSERT INTO mytable (dob) VALUES ('".$dateFormat."')";
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download