MySQL Date Format

Question

I have a field on my HTML form which asks the user to type the date as dd/mm/yyyy. I then run a PHP script that saves the data to a mySQL database, or it would if I could sort out an issue I have with the date format.

I know that by default the mySQL database saves dates as yyyy-mm-dd, but could someone perhaps tell me please how I can continue to keep the user input date format as dd/mm/yyyy but then when it hits the database it is correctly converted to the mySQL date format.

Answer 1

Use PHP to run your formatting. Before you insert it into the database, re-format the data for MySQL:

$mydate = new DateTime($userdate);
$mydate = $mydate->format("Y-m-d");

If you need to get it from the database, just change the date format back in a similar fashion.

Answer 2

I also came across this issue. My solution was to convert the string the user inputs to a Unix Timestamp and then using PHPs date() function to convert it to the MySQL-compliant format.

To convert the string to a Unix Timestamp, you could use strtotime or if you're familier with object oriented programming the DateTime::createFromFormat factory function. From my experience: strtotime will do the job.

Now you need to convert the timestamp you get back to the MySQL format. This is pretty straightforward:

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

Answer 3

The format of mysql is indeed 'YYYY-MM-DD'.

I suggest something like the following (untested):

$userInput = '24/12/2004';
date('c',  date_parse($userInput));

The sane thing to do would be to use prepared statements and send the date directly to the prepared statement. Alas, Php-MySQL prepared statements don't allow for a date type.

Answer 4

Try this:

php > list($d,$m,$y) = explode("/","28/04/2011");
php > echo date("Y-m-d",mktime(0,0,0,$m,$d,$y));
2011-04-28

Answer 5

You can do it by creating a simple function - something like this for example:

function convertDate2Sql($date = null) {
    if (!empty($date)) {
        $date = explode("/", $date);
        return implode("-", array_reverse($date));
    }
}

Obviously you have to make sure that if you are using different date format i.e. US one which has month before the day, then you'll have to manually rearrange the values inside of the $date array after using explode() function, but if it's in the same format as you've stated above then using array_reverse() will do the trick.

Answer 6

I found a jquery that allowed me to set the format of the date, enabling me to set the standard of the data stored in my database.