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.
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] |