Trucos Transact-SQL - Manejo de fechas

A menudo queremos usar una fecha independientemente de la hora. Aprendí un truco que en principio resuelve el problema:

select convert(datetime, convert(char, @fecha, 1))

Esto tiene un error y un inconveniente.

El error es que el primer convert (el de más afuera) asume que el formato default de entrada de fecha es mdy. Este formato se puede cambiar con la opción:

set dateformat {mdy | dmy | ymd | ydm | myd | dym}

El formato de conversión de char a datetime usado en el convert tiene que ser el mismo que el formato de conversión de datetime a char; el código correcto es:

select convert(datetime, convert(char, @fecha, 1), 1)

El inconveniente es la ineficiencia y falta de flexibilidad de la solución: Al truncar la parte horaria del datetime, la fecha retornada es la medianoche (es decir, el primer segundo del día) del día correspondiente a la fecha original. Las comparaciones de fechas que requieran incluir el día de la fecha fallan, incluso usando <=. La solución es simple: basta hacer:

select dateadd(day, 1, convert(datetime, convert(char, @fecha, 1), 1))

pero esto agrega a la ineficiencia y se convierte en complicado de entender.

Por otra parte, el manejo de strings es más pesado que el manejo de fechas (que en definitiva son números); la conversión ("parseo") del string a fecha es muy lento.

Hay una solución mejor, basada en el concepto de días transcurridos: la diferencia en días entre la fecha deseada y una fecha fija, no importa si en el pasado o en el futuro.

select datediff(day, 'Jan 1 1980', @fecha)

Esta técnica tiene dos aplicaciones:

• Truncar la parte horaria de la fecha
• Comparar fechas sin tener en cuenta la hora

Para un ejemplo de la segunda aplicación, supongamos que tenemos una tabla de logs con el siguiente diseño:

create table logs ( id int, dt datetime )

Si queremos buscar y contar todos los eventos anteriores a la fecha @fecha agrupadas por día, en vez de hacer:

select cnt = count(*),
dat = convert(datetime, convert(char, dt, 1), 1)
from logs
where dt <= convert(datetime, convert(char, @fecha, 1), 1)
group by convert(datetime, convert(char, dt, 1), 1)
order by cnt desc

hacer:

select cnt = count(*),
dat = dateadd(day, datediff(day, 'Jan 1 1980', dt), 'Jan 1 1980')
from logs
where datediff(day, dt, @fecha) >= 0
group by datediff(day, 'Jan 1 1980', dt)
order by cnt desc

El ahorro es de tres converts. El query ejecuta mucho más rápido porque las comparaciones son por entero en vez de por caracter.

Otra ventaja es que jugando con las fechas y/o horas base, podemos hacer que el truncado se haga con respecto a un momento arbitrario en el tiempo. Por ejemplo, para truncar la fecha al mediodía correspondiente, hacer:

select dateadd(day, datediff(day, 'Jan 1 1980', @fecha), 'Jan 1 1980 12:00:00PM')

Para truncar a la medianoche del día de mañana, hacer:

select dateadd(day, datediff(day, 'Jan 1 1980', @fecha), 'Jan 2 1980')

Tiene que quedar claro que las fechas base son completamente arbitrarias, y que, no importa cuál elijamos, o si es una fecha en el pasado o en el futuro, el resultado es siempre consistente.