Date format change to record and retrieve from MySQL


In a web application under jQuery , I'm using datepicker of jQuery UI .

At datepicker I apply a regional setting so that the date is delivered in dd/mm/yyyy format.

The user selects a date in the datepicker , which sent by Ajax to php to be recorded in MySQL and then, through another Ajax recovered the date that was recorded .

For example, the user selects 18/11/2016 in datepicker , but in MySQL when recording this date, it remains as 000-00-00 ; it's like he did not understand the date and recorded a 0 .

To solve the above, in the php that receives the date, I did the following:

$fechaz = $_POST["fecha"];
$partes = array();
$partes = explode("/",$fechaz);
$arreglo = array($partes[2], $partes[1], $partes[0]);
$nueva_fecha = implode("-", $arreglo);

$con = conectar();

$sql1 = "INSERT INTO fechas (fecha) VALUES ('$nueva_fecha')";
$q1 = mysqli_query( $con, $sql1

In this way, MySQL is now recorded in 2016-11-18 .

When you retrieve this date from MySQL in the next Ajax , and display it in another datepicker , a date appears that does not correspond 28/04/2022 . I tried to do the same in the php that gets the date, using explode() e implode() , but it does not work with the recovered date.

In this regard, the questions are:

Is there a way, through statements or functions of jQuery that allow, in the case of recording the date, transform the format of datepicker dd/mm/yyyy to the format required by MySQL yyyy-mm-dd , of so that in php you do not have to use explode() e implode() ?

And related to the same thing, is there a sentence or function that allows me to transform the format of the date that comes from MySQL yyyy-mm-dd and take it to dd/mm/yyyy , in order to display it correctly in datepicker ?

I have taken the suggestion of @aldanux and it worked for me to save the date in MySQL , but to present in the datepicker the recovered date, it does not work for me:

The invocation to record and retrieve the date is:

var fecha1 = $("#datepicker").val();

    url: "grabar_fecha.php",
    data: "fecha=" + fecha1,
    type: "POST",
    dataType: "json",
    success: function (respuesta) {

    url: "recupera_fecha.php",
    data: "saludo=" + "buscar",
    type: "POST",
    dataType: "json",
    success: function (res) {
        $("#datepicker").datepicker("setDate", res.fecha);

The% co_of% that you recorded was as:

require_once "funciones/conexiones.php";
$fechaz = $_POST["fecha"];
$f = str_replace('/', '-', $fechaz);
$fx = date('Y-m-d', strtotime($f));

$con = conectar();
$sql1 = "INSERT INTO fechas (fecha) VALUES ('$fx')";
$q1 = mysqli_query( $con, $sql1) or die("Problemas al ejecutar la consulta");

This is recording the date correctly, in my example php

The 2016-11-18 that recovers the date was as:

require_once "funciones/conexiones.php";

$saludo = $_POST['saludo'];
$con = conectar();

$sql = "SELECT max(id) as id, fecha FROM fechas";
$q = mysqli_query($con, $sql);
$info = array();

while ($datos = mysqli_fetch_array($q)) {

$fx = date('Y-m-d', strtotime($fec));
$info['id'] = $ident;
$info['fecha'] = $fx;
echo json_encode($info);

This is not working well, because the date you recover is php .

What is missing?

asked by Junco Fuerte 18.11.2016 в 16:47

6 answers


You can do it simply with the strtotime () function, set with date () from PHP.

See Demo

EDIT: I did not notice the / bar on the date:

To convert from / to - for the database, change it with the function str_replace () previously:

function format_date_dd_mm_Y($date)
    return date('d/m/Y', strtotime($date));

function format_date_Y_mm_dd($date)
    return date('Y-m-d', strtotime($date));

$fecha1 = '18/11/2016';
$fecha1 = str_replace('/', '-', $fecha1); // Cambia los '/' a '-'

$fecha2 = '2016-11-18';

echo format_date_Y_mm_dd($fecha1); // 18/11/2016 => 2016-11-18
echo format_date_dd_mm_Y($fecha2); // 2016-11-18 => 18/11/2016
answered by 18.11.2016 в 16:59

The Datepicker of jQuery UI widget, has the option to indicate altFormat and altField


altFormat : This allows a date format to be shown to the user for selection purposes, while a different format is actually sent behind the scenes.

Note : It is worth noting that altFormat is the format in which the date will be displayed on the selector specified in altField .

Example of use:

$(function() {
    altField: '#datepickerAlt',
    altFormat: 'dd/mm/yy',
    dateFormat: 'yy-mm-dd'
  $('#datepicker').datepicker('setDate', '2016-11-18');
.datepicker {
    position: relative;
.datepicker .datepicker-picker {
    position: absolute;
    opacity: 0
<!doctype html>
<html lang="en">
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <title>jQuery UI Datepicker - Default functionality</title>
  <link rel="stylesheet" href="//">
  <link rel="stylesheet" href="/resources/demos/style.css">
  <script src=""></script>
  <script src=""></script>
<div class="datepicker">
  <input type="text" class="datepicker-picker" id="datepicker">
  <input type="text" id="datepickerAlt">
answered by 18.11.2016 в 18:27

Try this:

$(function () {

    $("#tuFecha").datepicker({ dateFormat: 'yy/mm/dd' });

<script src=""></script>
<input type="text" id="tuFecha" />

Note: This example works in jsFiddle but not here, can you check it?

Here's the demo:


answered by 18.11.2016 в 16:56

I have a Helper for that, more or less as follows:

* Función que invierte el formato de la fecha
* @param string $fecha.
* @param string $parametro si no se envia un segundo valor,esta variable tiene predeterminado un guion '-'.
* @return string
static function fechaInvertida($fecha = '', $parametro = '-') {

    if ($fecha == '') {

        return date ( 'Y' . $parametro . 'm' . $parametro . 'd', $fecha );

    } else {

        $fechaTipoFormato = self::fechaTipoFormato ( $fecha, $parametro );

        if ($fechaTipoFormato [4] == '-' || $fechaTipoFormato [4] == '/') {

            return date ( 'd' . $fechaTipoFormato [4] . 'm' . $fechaTipoFormato [4] . 'Y', strtotime ( $fechaTipoFormato ) );

        } elseif ($fechaTipoFormato [2] == '-' || $fechaTipoFormato [2] == '/') {

            return date ( 'Y' . $fechaTipoFormato [2] . 'm' . $fechaTipoFormato [2] . 'd', strtotime ( $fechaTipoFormato ) );




In this way, you just pass the date and she makes the investment, regardless of whether you pass 2000-11-20 or 20-11-2000 will return the inverse.

But another simple way, would be to use the object Datetime of php . With another type function this:

 * @method fecha
 * @return date
static function fecha ($fecha = '') {

    $fecha = new DateTime($fecha);

    return $fecha->format('d-m-Y');


In both cases I have used them to return the MySQL format to the date as seen in Latin America.


answered by 18.11.2016 в 16:58

January 1, 1970 is the date of day 0 for Linux computing.

What shows that, from my point of view, what is wrong is this function in the recovery of the date, since I assume that the field of MySQL is timestamp:

$fx = date('Y-m-d', strtotime($fec));

Which would do it:

$fx = date('Y-m-d', $fec);
answered by 08.03.2017 в 18:45

This is recording the date correctly, in my example 2016-11-18

Ok, the problem is not to save, but to show and, in your code, you have it like this:

$fx = date('Y-m-d', strtotime($fec));

When it should be:

$fx = date('d/m/Y', strtotime($fec));

Now, I guess your code is not the real one, because in the cycle you only do assignments and until later you try to change the format. The best thing would be to see the complete code to know what is happening.

answered by 27.07.2017 в 05:34