Setting the Mysql DateFormat

Question

I want to change the data format in the mysql database like in this format 'April 11, 1979' this is what i got from the facebook API while retrieving from the facebook

So how to set the date format (April 11, 1979) while creating the mysql table.

Answer 1

You can't change the way that mysql store dates, instead you can change the date format returned from facebook and then store it in MYSQL, you can do something like this:

$fb_date = strtotime($date_returned_from_facebook);
$insert_in_my_sql = date('Y-m-d',$fb_date);

read more about PHP date: http://php.net/manual/en/function.date.php

Answer 2

If you just want to accept something like the specified string as a date imput format then this might help (see here: Parse date in MySQL):

STR_TO_DATE('April 11, 1979', '%M %e, %Y') AS date

With date formats explained here: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format .

Answer 3

You can use STR_TO_DATE function for this.

INSERT INTO test 
VALUES (1, STR_TO_DATE('April 11, 1979', '%M %d,%Y') ) ;

Fiddle - http://www.sqlfiddle.com/#!2/db30d/1