¿Cómo eliminar la parte de tiempo de un valor datetime (SQL Server)?

SQL Server 2008 y superiores

En SQL Server 2008 y superiores, por supuesto, la forma más rápida es Convert(date, @date). Esto puede ser lanzado de nuevo a datetime o datetime2 si es necesario.

¿Qué Es Realmente Mejor En SQL Server 2005 y Versiones Anteriores?

He visto afirmaciones inconsistentes sobre lo que es más rápido para truncar la hora desde una fecha en SQL Server, y algunas personas incluso dijeron que hicieron pruebas, pero mi experiencia ha sido diferente. Así que vamos a hacer algunas pruebas más estrictas y dejar todo el mundo tiene el guión así que si cometo algún error la gente puede corregirme.

Las Conversiones De Flotador No Son Precisas

Primero, me mantendría alejado de convertir datetime a float, porque no convierte correctamente. Puede que te salgas con la tuya haciendo lo de la eliminación de tiempo con precisión, pero creo que es una mala idea usarlo porque implícitamente comunica a los desarrolladores que esta es una operación segura y no lo es. Echa un vistazo:

declare @d datetime;
set @d = '2010-09-12 00:00:00.003';
select Convert(datetime, Convert(float, @d));
-- result: 2010-09-12 00:00:00.000 -- oops

Esto no es algo que deberíamos estar enseñando a la gente en nuestro código o en nuestros ejemplos en línea.

Además, ¡ni siquiera es la manera más rápida!

Pruebas de rendimiento

Si desea realizar algunas pruebas usted mismo para ver cómo los diferentes métodos realmente se apilan, entonces necesitará este script de configuración para ejecutar las pruebas más abajo:

create table AllDay (Tm datetime NOT NULL CONSTRAINT PK_AllDay PRIMARY KEY CLUSTERED);
declare @d datetime;
set @d = DateDiff(Day, 0, GetDate());
insert AllDay select @d;
while @@ROWCOUNT != 0
   insert AllDay
   select * from (
      select Tm =
         DateAdd(ms, (select Max(DateDiff(ms, @d, Tm)) from AllDay) + 3, Tm)
      from AllDay
   ) X
   where Tm < DateAdd(Day, 1, @d);
exec sp_spaceused AllDay;  -- 25,920,000 rows

Tenga en cuenta que esto crea una tabla de 427.57 MB en su base de datos y tardará unos 15-30 minutos en ejecutarse. Si su base de datos es pequeña y se establece en un crecimiento del 10% que tomará más tiempo que si el tamaño lo suficientemente grande primero.

Ahora para el script de pruebas de rendimiento real. Tenga en cuenta que es útil no devolver las filas al cliente, ya que esto es muy caro en 26 millones de filas y ocultaría las diferencias de rendimiento entre los métodos.

Resultados de rendimiento

set statistics time on;
-- (All queries are the same on io: logical reads 54712)
GO
declare
    @dd date,
    @d datetime,
    @di int,
    @df float,
    @dv varchar(10);

-- Round trip back to datetime
select @d = CONVERT(date, Tm) from AllDay; -- CPU time = 21234 ms,  elapsed time = 22301 ms.
select @d = CAST(Tm - 0.50000004 AS int) from AllDay; -- CPU = 23031 ms, elapsed = 24091 ms.
select @d = DATEDIFF(DAY, 0, Tm) from AllDay; -- CPU = 23782 ms, elapsed = 24818 ms.
select @d = FLOOR(CAST(Tm as float)) from AllDay; -- CPU = 36891 ms, elapsed = 38414 ms.
select @d = CONVERT(VARCHAR(8), Tm, 112) from AllDay; -- CPU = 102984 ms, elapsed = 109897 ms.
select @d = CONVERT(CHAR(8), Tm, 112) from AllDay; -- CPU = 103390 ms,  elapsed = 108236 ms.
select @d = CONVERT(VARCHAR(10), Tm, 101) from AllDay; -- CPU = 123375 ms, elapsed = 135179 ms.

-- Only to another type but not back
select @dd = Tm from AllDay; -- CPU time = 19891 ms,  elapsed time = 20937 ms.
select @di = CAST(Tm - 0.50000004 AS int) from AllDay; -- CPU = 21453 ms, elapsed = 23079 ms.
select @di = DATEDIFF(DAY, 0, Tm) from AllDay; -- CPU = 23218 ms, elapsed = 24700 ms
select @df = FLOOR(CAST(Tm as float)) from AllDay; -- CPU = 29312 ms, elapsed = 31101 ms.
select @dv = CONVERT(VARCHAR(8), Tm, 112) from AllDay; -- CPU = 64016 ms, elapsed = 67815 ms.
select @dv = CONVERT(CHAR(8), Tm, 112) from AllDay; -- CPU = 64297 ms,  elapsed = 67987 ms.
select @dv = CONVERT(VARCHAR(10), Tm, 101) from AllDay; -- CPU = 65609 ms, elapsed = 68173 ms.
GO
set statistics time off;

Algunos análisis Divagantes

Algunas notas sobre esto. En primer lugar, si sólo al realizar un GRUPO o una comparación, no hay necesidad de volver a convertir a datetime. Por lo tanto, puede ahorrar algo de CPU evitando eso, a menos que necesite el valor final para fines de visualización. Incluso puede AGRUPAR POR el valor no convertido y poner la conversión solo en la cláusula SELECT:

select Convert(datetime, DateDiff(dd, 0, Tm))
from (select '2010-09-12 00:00:00.003') X (Tm)
group by DateDiff(dd, 0, Tm)

También, ver cómo las conversiones numéricas solo toman un poco más de tiempo para convertir de nuevo a datetime, pero la conversión varchar casi se duplica? Esto revela la porción de la CPU que se dedica al cálculo de la fecha en las consultas. Hay partes del uso de la CPU que no implican el cálculo de la fecha, y esto parece ser algo cercano a 19875 ms en las consultas anteriores. Luego, la conversión toma una cantidad adicional, por lo que si hay dos conversiones, esa cantidad se consume aproximadamente dos veces.

Más examen revela que en comparación con Convert(, 112), la consulta Convert(, 101) tiene algún gasto de CPU adicional (ya que utiliza un varchar más largo?), porque la segunda conversión a date no cuesta tanto como la conversión inicial a varchar, pero con Convert(, 112) está más cerca del mismo costo base de CPU de 20000 ms.

Aquí están los cálculos sobre el tiempo de CPU que utilicé para el análisis anterior:

     method   round  single   base
-----------  ------  ------  -----
       date   21324   19891  18458
        int   23031   21453  19875
   datediff   23782   23218  22654
      float   36891   29312  21733
varchar-112  102984   64016  25048
varchar-101  123375   65609   7843

• round es el tiempo de CPU para un viaje de ida y vuelta a datetime.

• single es el tiempo de CPU para una sola conversión al tipo de datos alternativo (el que tiene el efecto secundario de eliminar el tiempo parte).

• base es el cálculo de la resta de single la diferencia entre las dos invocaciones: single - (round - single). Es una cifra aproximada que asume la conversión hacia y desde ese tipo de datos y datetime es aproximadamente la misma en cualquier dirección. Parece que esta suposición no es perfecta, pero es cercana porque los valores son todos cercanos a 20000 ms con una sola excepción.

Una cosa más interesante es que el costo base es casi igual al método único Convert(date) (que tiene que costar casi 0, ya que el servidor puede extraer internamente la porción de día entero de los primeros cuatro bytes del tipo de datos datetime).

Conclusión

Así que lo que parece es que el método de conversión de una sola dirección varchar toma alrededor de 1.8 µs y el método de una sola dirección DateDiff toma alrededor de 0.18 µs. Estoy basando esto en el tiempo de "CPU base" más conservador en mi prueba de 18458 ms en total para 25,920,000 filas, so 23218 ms / 25920000 = 0.18 µs. La aparente mejora de 10x parece mucho, pero es francamente bastante pequeña hasta que se trata de cientos de miles de filas (617k filas = 1 segundo de ahorro).

Incluso dada esta pequeña mejora absoluta, en mi opinión, el método DateAdd gana porque es la mejor combinación de rendimiento y claridad. La respuesta que requiere un" número mágico " de 0.50000004 va a morder a alguien algún día (¿cinco ceros o seis???), además es más difícil entender.

Notas complementarias

Cuando tenga algo de tiempo voy a cambiar 0.50000004 a '12:00:00.003' y ver cómo funciona. Se convierte al mismo datetime valor y me resulta mucho más fácil de recordar.

Para los interesados, las pruebas anteriores se ejecutaron en un servidor donde @ @ Version devuelve lo siguiente:

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Standard Edición en Windows NT 5.2 (Build 3790: Service Pack 2)