DATE o DATETIME en MySQL: el error del BETWEEN que se come el último día

Esta consulta parece correcta, se lee bien y hace exactamente lo que dice:

SELECT * FROM pedidos
WHERE fecha BETWEEN '2026-09-01' AND '2026-09-30';

Si fecha es DATE, lo es. Si es DATETIME, se deja fuera casi todo el 30 de septiembre. Es un error que he cometido yo, y no da ningún aviso: la consulta funciona, devuelve filas y los totales salen solo un poco más bajos de lo que deberían.

Qué coño pasa?

Cuando comparas una columna DATETIME con una fecha sin hora, MySQL completa la fecha con medianoche. La consulta de arriba, para el motor, es esta:

WHERE fecha BETWEEN '2026-09-01 00:00:00' AND '2026-09-30 00:00:00'

Un pedido del 30 a las 00:00:00 entra. Uno del 30 a las 09:15 ya no. Con datos así:

Lo mismo ocurre con LAST_DAY(), que devuelve una fecha sin hora, y con CURDATE(): WHERE fecha <= CURDATE() sobre un DATETIME no incluye nada de hoy salvo la medianoche exacta.

Los medio arreglos

• BETWEEN '2026-09-01' AND '2026-09-30 23:59:59': funciona con DATETIME a secas, pero si la columna guarda fracciones de segundo (DATETIME(3), DATETIME(6)), un registro de las 23:59:59.500 se queda fuera. Y hay que acordarse de ponerlo en cada consulta.
• WHERE DATE(fecha) BETWEEN …: el resultado es correcto, pero al aplicar una función a la columna MySQL ya no puede usar su índice y recorre la tabla entera. En una tabla pequeña no lo notas; en una de millones de filas, sí.

La forma buena

Un intervalo cerrado por abajo y abierto por arriba: desde el primer día, hasta antes del día siguiente al último.

SELECT * FROM pedidos
WHERE fecha >= '2026-09-01'
  AND fecha <  '2026-10-01';

Da lo mismo que la columna sea DATE, DATETIME o DATETIME(6); usa el índice; y si mañana alguien cambia el tipo de la columna, la consulta sigue siendo correcta. Yo ya no uso BETWEEN con fechas.

Para sacar el último día de un mes, y el mes pasado completo sin este problema: LAST_DAY() en MySQL: el último día del mes.