KP Joy KP Joy - 5 months ago 47
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?

Update:

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

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

<?php
     $mydate="18/06/2016";
     $date = DateTime::createFromFormat('d/m/Y', $mydate);
     $dateFormat=$date->format('Y-m-d');
     $sql="INSERT INTO mytable (dob) VALUES ('".$dateFormat."')";
Comments