MySQL y las fechas

El tipo de dato DATE se usa sólo cuando se desea trabajar con fechas y no con horas, trabaja con el formato (año-mes-día) y con un rango que va desde '1000-01-01' hasta '9999-12-31'. A la hora de insertar los registros podemos hacerlo de varias maneras, pero, será almacenado con el formato indicado antes. Ejemplo:

CREATE TABLE tabla_ejemplo (
id int(11) NOT NULL auto_increment PRIMARY KEY,
fecha DATE NOT NULL
)

INSERT INTO tabla_ejemplo (fecha) VALUES
('84-06-01'),('87.08.23'),('92/07/03'),('90.07.21'),('92/12/26'),('92-11-24');

Devuelve luego de un SELECT:

+----+------------+
| id | fecha      |
+----+------------+
|  1 | 1984-06-01 |
|  2 | 1987-08-23 |
|  3 | 1992-07-03 |
|  4 | 1990-07-21 |
|  5 | 2011-10-17 |
|  6 | 1992-11-24 |
+----+------------+

Se puede ver que al momento de insertar, se trabajó con el mismo formato para cada registro (año-mes-día). Ahora, si quisiéramos almacenarlo en un formato diferente, tipo (día-mes-año), dependerá de si ese campo se usará o no para filtros en las consultas o de si queremos o no hacer uso de funciones extras (se ve más adelante). Ejemplo:

CREATE TABLE tabla_ejemplo2 (
id int(11) NOT NULL auto_increment PRIMARY KEY,
fecha VARCHAR(10) NOT NULL
)

INSERT INTO tabla_ejemplo2 (fecha) VALUES
('01/06/1984'),('23/08/1987'),('03/07/1992'),('21/07/1990'),('26/12/1992'),('24/11/1992')

Devuelve después de un SELECT:

+----+------------+
| id | fecha      |
+----+------------+
|  1 | 01/06/1984 |
|  2 | 23/08/1987 |
|  3 | 03/07/1992 |
|  4 | 21/07/1990 |
|  5 | 26/12/1992 |
|  6 | 24/11/1992 |
+----+------------+

Más legible, pero ¿Qué pasa si ordenamos los resultados?

SELECT * FROM tabla_ejemplo ORDER BY fecha ASC;
+----+------------+
| id | fecha      |
+----+------------+
|  1 | 1984-06-01 |
|  2 | 1987-08-23 |
|  4 | 1990-07-21 |
|  3 | 1992-07-03 |
|  6 | 1992-11-24 |
|  5 | 1992-12-26 |
+----+------------+

SELECT * FROM tabla_ejemplo2 ORDER BY fecha ASC;
+----+------------+
| id | fecha      |
+----+------------+
|  1 | 01/06/1984 |
|  3 | 03/07/1992 |
|  4 | 21/07/1990 |
|  2 | 23/08/1987 |
|  6 | 24/11/1992 |
|  5 | 26/12/1992 |
+----+------------+

Como se puede observar, se muestra de forma correcta el resultado de la tabla 1 (tabla_ejemplo), y no así el resultado de la table 2 (tabla_ejemplo2), ya que (21/07/1990) no es menor que (23/08/1987), comparándolo como fecha, pero como la comparación se hace como cadena c, sucede que 21 siempre será menor que 23 y por eso independientemente del año y el mes, mostrara primero 10/03/2025 que 17/12/1830.

El formato (año-mes-día) puede no ser el más común a la hora de mostrar los resultados, MySQL nos facilita algunas funciones para jugar un poco con la presentación, por ejemplo, si quisiéramos presentarlas con el formato (día/mes/año), podemos usar la función DATE_FORMAT:

SELECT id, DATE_FORMAT(fecha, '%d/%m/%y') as fecha FROM tabla_ejemplo ORDER BY fecha;
+----+----------+
| id | fecha    |
+----+----------+
|  1 | 01/06/84 |
|  3 | 03/07/92 |
|  5 | 17/10/11 |
|  7 | 21/05/03 |
|  4 | 21/07/90 |
|  2 | 23/08/87 |
|  6 | 24/11/92 |
+----+----------+

Para hacer lo contrario usaríamos la función STR_TO_DATE.

SELECT id, STR_TO_DATE(fecha, '%d/%m/%Y') as fecha FROM tabla_ejemplo2 ORDER BY fecha;
+----+------------+
| id | fecha      |
+----+------------+
|  1 | 1984-06-01 |
|  2 | 1987-08-23 |
|  4 | 1990-07-21 |
|  3 | 1992-07-03 |
|  6 | 1992-11-24 |
|  5 | 1992-12-26 |
+----+------------+

---

Si por comodidad trabajamos en nuestro sistema con el formato (día/mes/año), no podríamos almacenarlo de esa manera en la base de datos si el campo destino es de tipo DATE, si se intenta insertar un valor incorrecto, el campo se establecerá a los valores 0000-00-00, incluso, si se intentara un UPDATE.

Para convertir los valores del formato (día/mes/año) a (año-mes-día) tendríamos que utilizar STR_TO_DATE en la inserción:

INSERT INTO tabla_ejemplo (fecha) VALUES (STR_TO_DATE('17/10/2011', '%d/%m/%Y'))

Otra opción es enviar los datos ya formateados desde PHP:

if ( ! function_exists('str_to_date'))
{   // convierte una cadena 'd/m/Y' a un valor de tipo date 'Y-m-d' 
    function str_to_date($sFecha,$delimitador='/'){

        if (empty($sFecha)) return false;
 
        $arrFecha = explode($delimitador,$sFecha);

        if (!count($arrFecha) == 3     or
            !ctype_digit($arrFecha[0]) or 
            !ctype_digit($arrFecha[1]) or 
            !ctype_digit($arrFecha[2]) or
            $arrFecha[0]>31            or 
            $arrFecha[1]>12            or
            $arrFecha[2]<1000            )
            return false;
        
        // si todo es correcto, 
        // devolvemos la fecha con el formato indicado
        return $arrFecha[2].'-'.$arrFecha[1].'-'.$arrFecha[0];
    }
}

// ejemplo
echo str_to_date('12/08/1989'); 
// devuelve: 1989-08-12
echo str_to_date('12-08-1989', '-'); 
// devuelve: 1989-08-12