[ADO.NET] Filtrar rango de fechas

 

Introducción

---

Uno de los principales problemas que se pueden encontrar cuando se confecciona una consulta es el trabajo con campos del tipo fecha.

Por lo general estos campos no solo persisten la fecha, sino que también registran la hora.

Es por eso que ciertas consultas pueden no retornar los valores deseados, aunque se este definiendo correctamente los parámetros de la consulta.

 

Planteo del problema

---

Para demostrar el problema se plantea el log de actividades del usuario, en donde se registras las acciones que este tiene en la aplicación. Pero el problema se presenta cuando se necesita consultar estos registros para analizar las acciones realizadas en un determinado rango de días, por supuesto se quiere ver los días completo, y es aquí donde nos encontramos con el inconveniente.

Si se analiza los registros de la tabla se ve que el registro de la fecha no solo usa el día, sino también la hora.

La aplicación de ejemplo cuenta con dos opciones de búsqueda, pero en este primer análisis nos centraremos en el primer botón de nombre “Buscar (sin usar CONVERT)”, al utilizarlo veremos el resultado de la imagen:

Pero con solo comparar los registros obtenido con los que se encuentran en la tabla, nos damos cuenta que hay un problema, esta retornando menos filas de las esperadas, la pregunta seria, porque sucedió esto ?

El código utilizado para obtener los registro es el siguiente :

public static LogActividades.RegistroActividadesDataTable GetFilterByDateRange(DateTime desde, DateTime hasta)
{
    LogActividades.RegistroActividadesDataTable dt = new LogActividades.RegistroActividadesDataTable();

    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["default"].ToString()))
    {
        conn.Open();

        string sql = @"SELECT Id, Descripcion, fecharegistro, usuario 
                        FROM RegistroActividades
                        WHERE fecharegistro >= @desde AND fecharegistro <= @hasta";

        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("@desde", desde);
        cmd.Parameters.AddWithValue("@hasta", hasta);

        SqlDataAdapter da = new SqlDataAdapter(cmd);
        da.Fill(dt);

    }

    return dt;
}

El parámetro de la fechas toma el valor directo de los controles que están en la pantalla, pero esta fecha aunque no se conozca también lleva consigo un componente de hora, que puede ser apreciado si se detiene el código y se analiza el valor:

El tipo de dato DataTime, lleva una hora aunque esta no se especifique concretamente.

Esto aclara bastante el porque la consulta arroja menos ítems de los esperados, resulta que esta quitando aquellos registros en donde la hora sea menos a las 12 AM

 

Solución del problema

---

La solución al problema se obtiene usando una funciona en la query, que aplicada sobre los campos de fecha en el filtro quiten la componente de la hora. En realidad no remueve la hora, sino que la normaliza para que esta también tenga las 12 AM, por lo tanto el filtro no descartara ningún registro.

Para esto se hará uso de la función CONVERT, la cual permite convertir entre tipos de datos.

CAST y CONVERT (Transact-SQL)

Si se ejecuta una consulta usando esta función se podría apreciar lo comentado mas arriba:

Las fechas de los registros dejan de tener la hora original, ahora todos presentan las 12 AM, esto es justamente lo que ayudara en el filtro.

Entonces si ahora se aplica lo comentado al código se obtiene el resultado esperado (usar el botón “Buscar (usando CONVERT)”):

Para que esto resulte se utilizo el siguiente código:

public static LogActividades.RegistroActividadesDataTable GetFilterByDateRangeRemoveHour(DateTime desde, DateTime hasta)
{
    LogActividades.RegistroActividadesDataTable dt = new LogActividades.RegistroActividadesDataTable();

    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["default"].ToString()))
    {
        conn.Open();

        string sql = @"SELECT Id, Descripcion, fecharegistro, usuario 
                        FROM RegistroActividades
                        WHERE CONVERT(smalldatetime, CONVERT(char(10), fecharegistro, 103), 103)  >= @desde 
                        AND CONVERT(smalldatetime, CONVERT(char(10), fecharegistro, 103), 103) <= @hasta";

        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("@desde", desde);
        cmd.Parameters.AddWithValue("@hasta", hasta);

        SqlDataAdapter da = new SqlDataAdapter(cmd);
        da.Fill(dt);
    }

    return dt;
}

El uso del CONVERT aplicado en los campo de “fecharegistro”, es quien quita la componente de la hora y permite aplicar el filtro correctamente.

 

Alternativa usando Between

---

Como alternativa al método anterior se podría lograr usando el Between en la query para filtrar por el rango de fechas.

En el Form2 del ejemplo se podrá encontrar la implementación de este caso.

public static LogActividades.RegistroActividadesDataTable GetFilterByDateRangeWithBetween(DateTime desde, DateTime hasta)
{
    LogActividades.RegistroActividadesDataTable dt = new LogActividades.RegistroActividadesDataTable();

    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["default"].ToString()))
    {

        string sql = @"SELECT Id, Descripcion, fecharegistro, usuario 
                        FROM RegistroActividades
                        WHERE CAST(CONVERT(CHAR(8), fecharegistro, 112) AS INT) BETWEEN CAST(CONVERT(CHAR(8), @desde, 112) AS INT) 
                                    AND CAST(CONVERT(CHAR(8), @hasta, 112) AS INT)";

        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("@desde", desde);
        cmd.Parameters.AddWithValue("@hasta", hasta);

        SqlDataAdapter da = new SqlDataAdapter(cmd);
        da.Fill(dt);
    }

    return dt;
}

La lógica principal se encontrara en el método creado para realizar la consulta, allí se ha utilizado el BETWEEN para definir el filtro entre un rango, pero este tienen un problema requiere de un formato especial que justamente el CONVERT (con el código 112) nos proporciona.

Esta conversión lleva la fecha al formato yyyyMMdd, o sea si tenemos la fecha 10/06/2010, será formateada a 20100610, claramente un numero, pero primero deberá pasar por un CHAR para adecuar la conversión del formato de la fecha y luego si será convertido a un valor numérico.

 

Ejemplo de Código

---

El código publicado fue desarrollado con visual Studio 2008.

La base de datos utilizada es el Sql Server Express 2008.

En el proyecto encontraran una carpeta de nombre “Script” que contiene el archivo .sql que podrán usar en el “Sql Server Management Studio” para crear la estructura de la base de datos en caso de tener problemas con el archivo .mdf

En caso de usar la base de datos integrada al servicio de Sql Server, se deberá cambiar la cadena de conexión definida en el App.config

 

[C#]

[VB.NET]