Convierta campos de fecha y hora del servidor SQL para comparar solo partes de fecha, con búsquedas indexadas

votos
28

He estado haciendo un convert(varchar,datefield,112)en cada campo de fecha que estoy usando en consultas 'entre' en el servidor SQL para asegurarme de que solo estoy contabilizando las fechas y no perder ninguna en función de la parte de tiempo de los campos de fecha y hora.

Ahora, estoy escuchando que las conversiones no son indexables y que hay mejores métodos, en SQL Server 2005, para comparar la parte de la fecha de las fechas en una consulta para determinar si las fechas caen en un rango.

¿Cuál es el método óptimo e indexable para hacer algo como esto?

select * from appointments
where appointmentDate>='08-01-2008' and appointmentDate<'08-15-2008'
Publicado el 09/12/2008 a las 15:59
fuente por usuario
En otros idiomas...                            


5 respuestas

votos
65

La mejor manera de quitar la porción de tiempo de un campo de fecha y hora es usar las funciones de fecha de vencimiento y de fecha y hora.

   DateAdd(day, datediff(day,0, MydateValue), 0)

Esto toma ventaja del hecho de que SQL Server almacena las fechas como dos enteros, uno que representa el número de días desde el día "0" - (1 de enero de 1900) y el segundo que representa el número de tics (cada marca es de aproximadamente 3,33 ms) ) desde la medianoche (por el momento) *.

la fórmula anterior simplemente tiene que leer el primer entero. No se requiere conversión o procesamiento, por lo que es extremadamente rápido.

Para hacer que sus consultas usen un índice ... primero use esta fórmula en los parámetros de filtrado de entrada, o en el lado "otro" del signo igual desde el campo de fecha y hora de las tablas, para que el optimizador de consultas no tenga que ejecutar el cálculo en cada campo de fecha y hora en la tabla para determinar qué filas satisfacen el predicado de filtro. Esto hace que su argumento de búsqueda sea "SARG-able" (Search ARGument)

Where MyDateTimeColumn > DateAdd(day, 
      datediff(day,0, @MydateParameter), 0)    -- SARG-able

más bien que

Where DateAdd(day, datediff(day,0, 
      MyDateTimeColumn ), 0) > @MydateParameter -- Not SARG-able

* NOTA. Internamente, el segundo entero (la parte de tiempo) almacena tics. En un día hay 24 x 60 X 60 X 300 = 25,920,000 tics (por casualidad, justo por debajo del valor máximo que puede contener un entero de 32 bits). Sin embargo, no necesita preocuparse por esto al modificar aritméticamente una fecha y hora ... Al agregar o restar valores de las fechas, puede tratar el valor como una fracción como si fuera exactamente igual a la porción fraccionaria de un día, como si el valor de fecha y hora completo era un número de punto flotante que consiste en una parte entera que representa la fecha y la parte fraccionaria que representa el tiempo). es decir,

`Declare @Dt DateTime  Set @Dt = getdate()  
 Set @Dt = @Dt + 1.0/24  -- Adds one hour  
 Select @Dt  
 Set @Dt = @Dt - .25 -- Moves back 6 hours  
 Select @Dt`
Respondida el 09/12/2008 a las 16:07
fuente por usuario

votos
5

Convertir los tipos numéricos en valores de cadena (un tipo de boxeo) no es el mejor método para realizar lo que estás buscando. No es realmente indexable, porque el tipo de columna real es la fecha y hora.

Si está buscando la mejor manera de buscar fechas, entonces su ejemplo es correcto, pero es posible que desee tener en cuenta la diferencia de precisión de 3 ms en MSSQL. Puede significar que los registros de un día pueden aparecer en el resultado de otro día.

Esta

select * from appointments where appointmentDate>='08-01-2008' and appointmentDate<'08-15-2008'

Debería ser esto

select * from appointments where appointmentDate>='08-01-2008' and appointmentDate<='08-14-2008 23:59:59.996'
Respondida el 09/12/2008 a las 16:11
fuente por usuario

votos
2

Es correcto: al hacer la conversión, se ejecutará la conversión para cada fila consultada. Es mejor dejar las columnas de fecha como fechas y pasar las cláusulas where como fechas:

select * from appointments where appointmentdate between 
'08/01/2008' AND '08/16/2008'

Nota: Salir de la hora significa medianoche (00: 00,000), por lo que incluirá todos los horarios para 08/01, y todos los horarios a partir de 08/15, y cualquier cosa que sea exactamente 08/16/2008 00:00:00

Respondida el 09/12/2008 a las 16:06
fuente por usuario

votos
1

Haga que una columna calculada persista calcule la expresión que necesita. Si las columnas se calculan y persisten, también se pueden indexar.

Respondida el 09/12/2008 a las 16:10
fuente por usuario

votos
0

También existe la manera descrita en http://www.stillnetstudios.com/comparing-dates-without-times-in-sql-server/

SELECT CAST(FLOOR(CAST( getdate() AS float )) AS datetime)
Respondida el 28/09/2011 a las 13:31
fuente por usuario

Cookies help us deliver our services. By using our services, you agree to our use of cookies. Learn more