Cláusulas WHERE de SQL con intervalos de fechas: ¿qué es lo mejor?

Examina la forma en que se manejan las fechas dentro de las cláusulas WHERE de SQL Server, y la mejor manera de utilizarlas

Contexto

Debido a la legislación sobre protección de datos, determinados datos deben eliminarse de los sistemas en un plazo determinado, que en la mayoría de los casos es de siete años.

Al investigar esto en las instalaciones de un cliente, obtenían resultados extraños cuando comprobábamos la consulta que nos habían enviado.

SQL

SELECT *FROM AccountsWHERE DATEDIFF(YEAR,ClosedDate,GETDATE())>=7

Crear una tabla de prueba

Ahora esto trajo lo que parecía el volumen correcto, sin embargo en una inspección más detallada había más cuentas en comparación con si yo hiciera una fecha estática siete años antes, entonces, ¿por qué lo hace?

He creado el siguiente código para investigarlo.

SQL

DECLARE @Records TABLE (RecordID INT,RecordClosed DATETIME)DECLARE @InsRecID INT = 1DECLARE @InsRecDate DATE=DATEADD(YEAR,-8,GETDATE())WHILE @InsRecDate      INSERT INTO @Records      SELECT @InsRecID,@InsRecDate      SET @InsRecID = @InsRecID+1      SET @InsRecDate=DATEADD(DAY,1,@InsRecDate)ENDDECLARE @DateFrom DATE=DATEADD(YEAR,-7,GETDATE())SELECT MAX(RecordClosed) FROM @RecordsSELECT MAX(RecordClosed) FROM @Records WHERE DATEDIFF(YEAR,RecordClosed,getdate())>=7SELECT MAX(RecordClosed) FROM @Records WHERE RecordClosed<@DateFromSELECT MAX(RecordClosed) FROM @Records WHERE RecordClosed<=@DateFromSELECT MAX(RecordClosed) FROM @Records WHERE DATEDIFF(DAY,RecordClosed,getdate())/365.25>7

Probando

Esto traerá los siguientes resultados cuando se ejecuta el 21/03/2013

Resultados

2007-03-21 00:00:00.000 2006-12-31 00:00:00.000 2006-03-20 00:00:00.000 2006-03-21 00:00:00.0002006-03-21 00:00:00.000

¿Qué es correcto?

El primer registro es sólo la fecha más grande en la tabla temporal, así que está bien.

El segundo registro ha recogido todas las cuentas hasta finales de 2006, esto podría haber sido un problema potencial ya que se habrían eliminado demasiados datos. DATEDIFF en el año es puramente la comprobación de que el año es hace 7 años.

El tercer y cuarto registro son los que queremos, dependiendo de si se quiere incluir el día de hace siete años dentro o fuera.

El quinto registro, aunque es correcto cuando se ejecuta, está utilizando cálculos de año bisiesto, y en aras de la exactitud no se podría confiar si tuviera que ser específico.

Diferencia de velocidad

Mientras comprobaba los registros, lo ejecuté en el sistema comprobando 2.500.000 registros para 3 y 5 arriba.

El enunciado 5 devolvió nuestro resultado en 6 segundos, el enunciado 3 en sólo 3 segundos, así que no sólo estábamos ahora seguros de que éramos precisos, sino que también procesábamos los registros más rápidamente.

Intenta usar una fecha adecuada cuando consultes fechas, espero que esto ayude a alguien que se esté rascando la cabeza.