Convert Date To sql formate in php and store it to database

Question

I have date time picker in html that return me the date when i retrieve data in php page

$date=$_POST['date'];
echo $date;

it return the output in this format

13 November 2015

i want to convert it to store in mysql table

how can i convert it to sql format to store because when i am trying to store this date mysql stores 0000-00-00 by default. i can not take data type varchar because i have to create views according to date.

also i need the code to convert it again to this format to show data in this format.

Answer 1

use MySQL Str_To_Date :-

SELECT str_to_date('13 November 2015','%d %M %Y')

or try :-

$date=$_POST['date'];

insert into table (col) values (str_to_date($date,'%d %M %Y'))

or use PHP strtotime

$time = strtotime('13 November 2015');
$newformat = date('Y-m-d',$time);

Answer 2

If you are using PHP > 5.2, then you should use following way.

$date=$_POST['date'];  //13 November 2015
echo $date;
$myDateTime = DateTime::createFromFormat('j F Y', $date);
$newDateString = $myDateTime->format('Y-m-d');
echo $newDateString;    //2015-11-13

You should use this in try...catch block as it throw exception on failure.

Answer 3

You can convert your date to timestamp with strtotime.

Then you can convert it to format you like with date function.

$date = $_POST['date'];
$timestamp = strtotime($date);
$converted_date = date("Y-m-d H:i:s", $timestamp);
// var_dump($converted_date);

Answer 4

Step 1: Extract time variables(used in step #2)

Step 2: Create timestamp using the above info

$timestamp = mktime($hour, $min, $sec, $month, $day, $year);

Step 3: Use PHP's date function:

echo date("Y-m-d", $timestamp);

Reference: http://php.net/manual/en/function.date.php#example-2514