FILTRAR POR RANGO DE FECHAS

Que tal amigos, hoy les mostraré lo que algunos consideran un BUG al momento de filtrar datos por una columna tipo DATETIME.

Primero, creamos una tabla de prueba con algunos datos, les dejo un LINK con el script o el script directamente para copiar.

IF OBJECT_ID (N'pruebas', N'U') IS NOT NULL
DROP TABLE pruebas
CREATE TABLE pruebas( id INT IDENTITY( 1,1 ), fecha DATETIME )

INSERT INTO pruebas( fecha )
VALUES ('2012-12-01 00:04:40'), ('2012-12-01 05:35:29'), ('2012-12-01 08:54:10'),('2012-12-01 09:04:34'), 
('2012-12-01 09:37:48'), ('2012-12-01 09:39:55'), ('2012-12-01 10:28:14'),('2012-12-01 11:46:13'), 
('2012-12-01 12:01:40'), ('2012-12-01 13:22:10'), ('2012-12-01 13:27:37'),('2012-12-01 13:35:01'), 
('2012-12-01 14:40:25'), ('2012-12-01 15:01:15'), ('2012-12-01 15:36:44'),('2012-12-01 16:39:27'), 
('2012-12-01 17:21:07'), ('2012-12-01 17:46:49'), ('2012-12-01 17:54:16'),('2012-12-01 18:10:10'), 
('2012-12-01 19:58:17'), ('2012-12-01 20:05:47'), ('2012-12-01 20:31:41'),('2012-12-01 22:19:10'), 
('2012-12-02 09:02:21'), ('2012-12-02 10:45:44'), ('2012-12-02 11:58:16'),('2012-12-02 14:21:06'), 
('2012-12-02 17:07:40'), ('2012-12-02 17:44:29'), ('2012-12-02 20:50:20'),('2012-12-03 07:47:26'), 
('2012-12-03 08:06:27'), ('2012-12-03 08:29:54'), ('2012-12-03 08:42:01'), ('2012-12-03 09:24:39'), 
('2012-12-03 09:26:37'), ('2012-12-03 09:30:15'), ('2012-12-03 09:48:14'), ('2012-12-03 09:55:21'), 
('2012-12-03 10:16:13'), ('2012-12-03 10:48:05'), ('2012-12-03 10:55:11'), ('2012-12-03 10:55:23'), 
('2012-12-03 10:56:33'), ('2012-12-03 10:57:39'), ('2012-12-03 11:02:07'), ('2012-12-03 11:08:28'),
('2012-12-03 11:21:47'), ('2012-12-03 11:22:33'), ('2012-12-03 11:26:58'), ('2012-12-03 11:36:33'),
('2012-12-03 11:52:40'), ('2012-12-03 12:12:28'), ('2012-12-03 12:20:56'), ('2012-12-03 12:28:10'),
('2012-12-03 12:42:23'), ('2012-12-03 12:52:23'), ('2012-12-03 13:11:18'), ('2012-12-03 13:23:37'),
('2012-12-03 13:23:46'), ('2012-12-03 13:32:52'), ('2012-12-03 13:33:00'), ('2012-12-03 13:49:14'),
('2012-12-03 14:30:19'), ('2012-12-03 14:46:38'), ('2012-12-03 15:14:41'), ('2012-12-03 15:31:47'),
('2012-12-03 16:03:05'), ('2012-12-03 17:19:39'), ('2012-12-03 17:41:06'), ('2012-12-03 17:54:31'),
('2012-12-03 18:33:24'), ('2012-12-03 21:32:30'), ('2012-12-04 08:22:47'), ('2012-12-04 08:27:28'),
('2012-12-04 08:43:58'), ('2012-12-04 08:59:31'), ('2012-12-04 09:22:33'), ('2012-12-04 09:28:17'),
('2012-12-04 09:35:36'), ('2012-12-04 10:04:03'), ('2012-12-04 10:53:45'), ('2012-12-04 11:07:15'),
('2012-12-04 11:12:53'), ('2012-12-04 11:26:23'), ('2012-12-04 11:30:38'), ('2012-12-04 11:42:46'),
('2012-12-04 12:32:57'), ('2012-12-04 12:40:16'), ('2012-12-04 12:59:17'), ('2012-12-04 13:00:09'),
('2012-12-04 13:08:13'), ('2012-12-04 13:09:56'), ('2012-12-04 13:23:39'), ('2012-12-04 13:26:42'),
('2012-12-04 13:51:10'), ('2012-12-04 14:57:20'), ('2012-12-04 15:20:42'), ('2012-12-04 15:37:18'),
('2012-12-04 16:14:04'), ('2012-12-04 16:16:06'), ('2012-12-04 17:21:32'), ('2012-12-04 17:28:54'),
('2012-12-04 17:42:00'), ('2012-12-04 18:04:24'), ('2012-12-04 18:05:48'), ('2012-12-04 19:13:49'),
('2012-12-04 19:23:38'), ('2012-12-04 21:27:58'), ('2012-12-04 22:42:20'), ('2012-12-05 08:48:58'),
('2012-12-05 09:00:59'), ('2012-12-05 09:24:29'), ('2012-12-05 10:09:48'), ('2012-12-05 10:35:41'),
('2012-12-05 10:39:00'), ('2012-12-05 11:21:44'), ('2012-12-05 11:35:34'), ('2012-12-05 12:28:42'),
('2012-12-05 12:31:30'), ('2012-12-05 12:54:57'), ('2012-12-05 13:30:31'), ('2012-12-05 13:39:06'),
('2012-12-05 13:43:25'), ('2012-12-05 13:47:54'), ('2012-12-05 13:59:07'), ('2012-12-05 14:19:53'),
('2012-12-05 14:34:23'), ('2012-12-05 14:42:55'), ('2012-12-05 15:13:39'), ('2012-12-05 15:52:14'),
('2012-12-05 15:58:17'), ('2012-12-05 16:13:00'), ('2012-12-05 16:17:26'), ('2012-12-05 18:02:29'),
('2012-12-05 19:21:14'), ('2012-12-05 19:21:53'), ('2012-12-05 21:13:34'), ('2012-12-05 22:16:08')

En total son 140 filas, agrupando por día, tenemos como resultado lo siguiente:

SELECT CAST( fecha AS DATE ) as dia, COUNT( id ) as total
FROM pruebas
GROUP BY CAST( fecha AS DATE )

Como podemos observar, para el día 1 y 2 de Diciembre tenemos 24 y 7 filas( 31 en total ), ahora ejecutemos la siguiente consulta:

SELECT *
FROM pruebas
WHERE fecha BETWEEN '20121201' AND '20121202'

Como podemos observar solo arroja 24 filas, y que pasó con las 7 filas restantes??? Si observamos detenidamente la consulta:

SELECT *
FROM pruebas
WHERE fecha BETWEEN '20121201' AND '20121202'

La columna fecha es de tipo DATETIME, pero que tipo de dato son los filtros que estamos aplicando? Ejecutemos la siguiente consulta:

SELECT CAST( '20121201' AS DATETIME ) as fecha1
, CAST( '20121202' AS DATETIME ) as fecha2

Sorpresa? No, porque está es la forma en que el motor maneja las fechas, observemos y ejecutemos la siguiente consulta:

DECLARE @hoy AS DATETIME
SET @hoy = GETDATE()
SELECT @hoy AS fechaDATETIME
, CAST( @hoy AS DATE ) AS fechaDATE
, CAST( CAST( @hoy AS DATE ) AS DATETIME ) AS fecha

Mostramos la fecha hora actual que es de tipo DATETIME, lo convertimos a DATE y nuevamente a DATETIME, como podemos observar la hora actual no es la misma.

Perfecto, ya quedó comprendido, debo utilizar un tipo de dato DATETIME para hacer el filtro de la siguiente manera:

SELECT *
FROM pruebas
WHERE fecha BETWEEN '20121201 00:00' AND '20121202 23:59'

Claro, quedó solucionado, ya podemos filtrar por fechas, pero... que pasa si agregamos una fila con la hora 23:59:59

INSERT INTO pruebas VALUES( '2012-12-02 23:59:59' )

Y ejecutamos nuevamente la consulta de agrupación para observar cuantas filas tenemos por día.

SELECT CAST( fecha AS DATE ) as dia, COUNT( id ) as total
FROM pruebas
GROUP BY CAST( fecha AS DATE )

Observemos que ya tenemos 8 filas en el día 2 y apliquemos el query para extraer las filas del día 1 y 2 nuevamente:

SELECT *
FROM pruebas
WHERE fecha BETWEEN '20121201 00:00' AND '20121202 23:59'

Y ahora que paso? si ya aplicamos la consulta con los filtros fecha-hora... Ok, esto es cierto, pero recordemos que el tipo DATETIME en SQL SERVER almacena hasta milésimas de segundo, por lo tanto en la consulta debemos especificar hasta la ultima milésima de segundo del día 2, en este caso solo manejamos hasta segundos:

SELECT fecha
FROM pruebas
WHERE fecha BETWEEN '20121201 00:00' AND '20121202 23:59:59'

Ahora, hagamos la prueba agregando una fila con milésimas de segundo:

INSERT INTO pruebas VALUES( '2012-12-02 23:59:59.997' )

Ejecutamos la consulta de agrupación:

SELECT CAST( fecha AS DATE ) as dia, COUNT( id ) as total
FROM pruebas
GROUP BY CAST( fecha AS DATE )

Y ahora ya tenemos 9 filas en el día 2 y para poder obtener esas filas es necesario especificar el ultimo milisegundo del día 2 de la siguiente manera:

SELECT fecha
FROM pruebas
WHERE fecha BETWEEN '20121201 00:00' AND '20121202 23:59:59.999'

Ok, comprendido, pero también esto lo consigo entonces filtrando de la siguiente manera???:

SELECT fecha
FROM pruebas
WHERE fecha BETWEEN '20121201' AND '20121203'

Ya que 2012-12-03 es igual a 2012-12-03 00:00:00.000 porque SQL SERVER así lo interpreta cuando se comparan DATE y un DATETIME.

ERROR, esto funcionará siempre y cuando no tengamos una fila con fecha 2012-12-03 00:00:00.000

INSERT INTO pruebas VALUES( '2012-12-03 00:00:00.000' )

Si ejecutamos el mismo query, obtenemos lo siguiente:

Esta bien, ya comprendí, entonces esto lo puedo lograr convirtiendo la columna a tipo DATE y filtrando solo por el día sin horas, minutos y segundos??

Respuesta: SI, pero no es recomendable. Al hacer esto afectaremos la búsqueda del query, supongamos que nuestra tabla está indexada por este campo:

CREATE NONCLUSTERED INDEX IXfecha ON pruebas (fecha);

Después activamos la Inclusión del Plan de Ejecución en el resultado de la consulta, a través de CTRL + M  o en el botón que describe la siguiente imagen:

Y ejecutamos la consulta:

SELECT fecha
FROM pruebas
WHERE fecha BETWEEN '20121201 00:00' AND '20121202 23:59:59.999'

Como podemos observar, hay una pestaña más en nuestro resultado que es el Plan de Ejecución y además vemos una gráfica del proceso que hizo el motor para obtener el resultado; Al ver un Index Seek no nos debemos preocupar, esto quiere decir que el motor está buscando o filtrando por un Índice, lo cual es correcto. Pero que pasa si convertimos la columna en tipo DATE para poder lograr lo que unos pasos antes comentabamos:

SELECT fecha
FROM pruebas
WHERE CAST( fecha AS DATE)  BETWEEN '20121201' AND '20121202'

Si, también obtendremos las mismas 33 filas pero a que costo, observemos el plan de ejecución de la consulta nuevamente:

Hizo más pasos para obtener las mismas filas, entonces esto quiere decir que nuestra consulta no está del todo bien, más adelante les explicaré que significa tantas imágenes en el plan de ejecución.

Esto sucede porque al aplicar una función sobre una columna que se encuentra indexada, esta pierde su propiedad y estaremos buscando sobre una columna NO INDEXADA.

Ok ok, ya comprendí, pero entonces que puedo hacer para obtener esos 33 registros sin afectar el rendimiento del query???

Respuesta: Volver a los operadores mayor o igual que( >= ) y menor ( < ) de la siguiente manera:

SELECT fecha
FROM pruebas
WHERE fecha >= '20121201' AND fecha < '20121203'

Observemos el Plan de Ejecución ( CTRL + M ) 

De esta forma estaremos tomando las filas que tengan 2012-12-01 00:00:00.000 y además aquellas que sean menores a 2012-12-03 00:00:00.000 como 2012-12-02 23:59:59.999.

En resumen, debemos de conocer cómo funciona el tipo de dato DATETIME para poder aplicar un BETWEEN, en caso contrario lo mejor siempre será utilizar los operadores mayor o igual que( >= ) y menor ( < ).

SALUDOS!

COMPARTE ESTA INFORMACION SI TE PARECIO INTERESANTE