Consultas SQL basadas en fecha y/o hora

Manejando SQL Server 2005, me encontre que tratando hacer consultas basadas en fechas, obtenía como resultado nada (empty). Esto se debía a que no tenía claro como trabajan los tipos de dato datetime y smalldatetime. Estos dos tipos de datos son muy similares, se diferencia en que datetime es más detallado a la hora de almacenar la fecha. Estas diferencias pueden ser vistas en la siguiente tabla:

Tipo	Minimo	Maximo	Precision
datetime	Ene 1, 1753 media-noche	Dic 31, 9999 23:59:59.997 (0.003 segundos hasta la media-noche)	Más cercano 3.33 millisegundos
smalldatetime	Ene 1, 1900 media-noche	Jun 6, 2079 23:59 (un minuto hasta la media-noche)	Más cercano al mínuto

Ambos, representan una fecha y hora que es igual al número de días en relación a una fecha base. En SQL Server la fecha base es la medianoche del 1 de Enero de 1900. Observando la tabla, el tipo de dato smalldatetime, puede solo representar fechas desde esta fecha base, en cambio el datetime puede representar fechas antes del 1 de enero de 1900. Para hacer esto, el almacena el valor como número negativo.

Para visualizar, como estos valores son guardados.
Piensa en un valor decimal, cuya parte entera representa los días desde la fecha base y la parte fraccional (los numeros después del punto) representan la hora. Por ejemplo, el valor date/time mediodía del 4 de enero de 1900, es almacenado como 3.5, donde 3, representa tres días desde la fecha base y el 0.5, representa la mitad del día. Para ver esto en el SQL Server, ejecuta la siguiente consulta:
SELECT CAST(CAST('1900-01-04 12:00' AS datetime) AS float)

Otro datito importante es saber que el SQL Server no provee un tipo de dato que solo almacene fecha u hora.  Es decir, que si tu almacenas un valor sin la hora (la parte fraccional) se le asigna el valor de cero (0). De igual forma, si tu almacenas un valor sin la fecha, la parte entera se le asigna automáticamente el valor de cero (0), que para la fecha significa 1 de enero de 1900. Para ejemplo, ejecuta la siguiente consulta:

SELECT CAST('1900-01-04' AS datetime), CAST('10:00' AS datetime)

el cual retornará el siguiente resultado:

1900-01-04 00:00:00.000         1900-01-01 10:00:00.000

Como Buscar Solo la Fecha

Para ilustrar usaremos la siguiente tabla:

ID  DateVal
--  -----------------------
1   2001-02-28 10:00:00.000
2   2002-02-28 13:58:32.823
3   2002-02-29 00:00:00.000
4   2002-02-28 00:00:00.000

 Si realizamos la siguiente consulta:

SELECT * FROM DateSample WHERE DateVal = '2002-02-28'

El resultado solo traerá la fila #4, esto se debe a que el SQL Server buscará los datos cuya hora sea 00:00:00 (medianoche). El valor literal buscado (28 de febrero de 2002) implicítamente se le agregará la hora antes en mención. Entonces, como podemos evitar este suceso y obtener todos los registros cuya fecha sea 28 de febrero de 2002? Lo haremos de la siguiente forma:

Si la consulta es ejecuta frecuentemente, tú debes basar tu búsqueda en un rango de fechas, como la siguiente:

SELECT * FROM DateSample WHERE DateVal BETWEEN '2002-02-28' AND '2002-02-28 23:59:59.997'

Nota: Ahora pensarás que yo soy un brutón porque debi usar como límite superior '2002-02-29' en vez de '2002-02-28 23:59:59.997'. Pués no, si haces esto, la fila #3 será parte del resultado de tu consulta.

Otra forma de obtener el resultado de manera más sencilla (la que yo recomiendo), es de la siguiente manera:

SELECT * FROM DateSample WHERE DateVal >= '2002-02-28' AND DateVal < '2002-02-29'

Como Buscar Solo por la Hora (How to search by time)

Para ilustrar, consideremos la siguiente tabla:

ID  TimeVal
--  -----------------------
1   2002-02-28 10:00:00.000
2   1900-01-01 13:58:32.823
3   1900-01-01 09:59:59.997
4   1900-01-01 10:00:00.000

Si usamos la siguiente consulta para extraer las filas cuya hora es igual a las 10:00 AM:

SELECT * FROM TimeSample WHERE TimeVal = '10:00:00'

solo conseguiremos la fila #4. La fila #1 no será parte del resultado, debido a que sucede lo mismo que con la fecha, pero en este caso el valor que es implicítamente agregado, es la fecha. En adición, la fila #3 no es parte del resultado porque es un valor super cerca, pero no igual a las 10:00 AM.

Para obviar la fecha, tu puedes crear una consulta que corte la parte entera del valor date/time, y solo deje la parte fraccional. Esta sería de la siguiente forma:

SELECT * FROM TimeSample WHERE TimeVal - CAST(FLOOR(CAST(TimeVal AS float)) AS datetime) = '10:00' 

Esta consulta nos devolvería la fila 1 y 4. Lamentablemente, no hay otra forma de conseguir este resultado sin usar esta combinación de formulas. Por tal motivo, hay que tomar mucho cuidado como se almacena la data datetime/smalldatetime que solo se usa para horas (time-only). Si tú necesitas realizar frecuentemente este tipo de búsqueda, te recomiendo que analices y reestructures el diseño de tu base de datos.

Si tú deseas hacer consultas basadas solo por horas para un intervalo de tiempo, bastaría con una simple consulta (query), esto; tomando en cuenta que se ha almacenado la data hora (time-only) consistentemente sin el componente fecha. La consulta sería algo como esto:

SELECT * FROM TimeSample WHERE TimeVal BETWEEN '09:59' AND '10:01'

o

SELECT * FROM TimeSample WHERE TimeVal > '09:59' AND TimeVal < '10:01' 

Estas dos consultas retornarían los datos de la fila #3 y #4.

Si los datos de hora (time-only) han sido guardados en la base de datos inconsistemente, entonces se necesitará que la consulta evite o excluya la parte fecha de estos. Por ejemplo, tu podrías usar una consulta como esta:

SELECT * FROM TimeSample WHERE TimeVal - CAST(FLOOR(CAST(TimeVal AS float)) AS datetime) > '09:59' AND TimeVal - CAST(FLOOR(CAST(TimeVal AS float)) AS datetime) < '10:01'

la cual nos devuelve las filas 1, 3 y 4. Recuerden que no podrías realizar correctamente la búsqueda sin estas funciones, cuando la data hora ha sido guardada inconsistentemente.

Nota: Toda esta información ha sido traducida del libro Murach's SQL for SQL Server del autor Bryan Syverson.