separar - ¿Cómo eliminar la parte de tiempo de un valor de fecha y hora(SQL Server)?

obtener hora sql server

Question

Esto es lo que uso:

SELECT CAST(FLOOR(CAST(getdate() as FLOAT)) as DATETIME)

Estoy pensando que puede haber una manera mejor y más elegante.

Requisitos:

• Tiene que ser lo más rápido posible (cuanto menos lanzamiento, mejor).
• El resultado final tiene que ser un tipo de datetime y datetime , no una cadena.

Answer 1

Itzik Ben-Gan en DATETIME Calculations, Part 1 (SQL Server Magazine, febrero de 2007) muestra tres métodos para realizar dicha conversión (de más lenta a más rápida ; la diferencia entre el segundo y el tercer método es pequeña):

SELECT CAST(CONVERT(char(8), GETDATE(), 112) AS datetime)

SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

SELECT CAST(CAST(GETDATE() - 0.50000004 AS int) AS datetime)

Su técnica (casting para flotar ) es sugerida por un lector en la edición de abril de la revista. Según él, tiene un rendimiento comparable al de la segunda técnica presentada anteriormente.

Answer 2

Por favor, inténtalo:

SELECT CONVERT(VARCHAR(10),[YOUR COLUMN NAME],105) [YOURTABLENAME]

Answer 3

SQL Server 2008 tiene un nuevo tipo de datos de fecha y esto simplifica este problema para:

SELECT CAST(CAST(GETDATE() AS date) AS datetime)

Answer 4

SQL2005: recomiendo emitir en lugar de dateadd. Por ejemplo,

select cast(DATEDIFF(DAY, 0, datetimefield) as datetime)

promedió alrededor del 10% más rápido en mi conjunto de datos, que

select DATEADD(DAY, DATEDIFF(DAY, 0, datetimefield), 0)

(y la conversión a smalldatetime fue aún más rápida)

Answer 5

Su CAST - FLOOR - CAST ya parece ser la forma óptima, al menos en MS SQL Server 2005.

Algunas otras soluciones que he visto tienen una conversión de cadena, como Select Convert(varchar(11), getdate(),101) , que es más lenta por un factor de 10.

Answer 6

SQL Server 2008 y hasta

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

¿Qué es realmente mejor en SQL Server 2005 y versiones anteriores?

He visto afirmaciones inconsistentes sobre qué es lo más rápido para truncar el tiempo desde una fecha en SQL Server, y algunas personas incluso dijeron que hicieron pruebas, pero mi experiencia ha sido diferente. Así que hagamos algunas pruebas más estrictas y dejemos que todos tengan el guión para que, si cometo algún error, la gente pueda corregirme.

Las conversiones de flotación no son precisas

Primero, me mantendría alejado de convertir datetime a float , porque no se convierte correctamente. Puede salirse con la suya al hacer la eliminación de tiempo con precisión, pero creo que es una mala idea usarlo porque comunica implícitamente a los desarrolladores que esta es una operación segura y no lo es . Echar 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 enseñar a las personas en nuestro código o en nuestros ejemplos en línea.

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

Prueba: pruebas de rendimiento

Si desea realizar algunas pruebas usted mismo para ver cómo se comparan realmente los diferentes métodos, 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á entre 15 y 30 minutos en ejecutarse. Si su base de datos es pequeña y tiene un crecimiento del 10%, llevará más tiempo que si primero dimensiona lo suficientemente grande.

Ahora para el script de prueba de rendimiento real. Tenga en cuenta que es útil no devolver filas al cliente, ya que esto es muy costoso 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 de divagación

Algunas notas sobre esto. En primer lugar, si solo realiza un GROUP BY o una comparación, no es necesario volver a la datetime y datetime . Por lo tanto, puede guardar 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)

Además, ¿ve cómo las conversiones numéricas solo tardan un poco más de tiempo en volver a la datetime y datetime , pero la conversión varchar casi se duplica? Esto revela la parte 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 usa aproximadamente dos veces.

Un examen más detallado revela que, en comparación con Convert(, 112) , la consulta Convert(, 101) tiene algunos gastos adicionales de CPU (¿ya que utiliza un varchar más largo?), Porque la segunda conversión a la date no cuesta tanto como la inicial conversión a varchar , pero con Convert(, 112) está más cerca del mismo costo base de CPU de 20000 ms.

Aquí están esos 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 única conversión al tipo de datos alternativo (el que tiene el efecto secundario de eliminar la porción de tiempo).

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

Una cosa más interesante es que el costo base es casi igual al método simple Convert(date) (que tiene que ser un costo casi 0, ya que el servidor puede extraer internamente la parte del día entero directamente de los primeros cuatro bytes de los datos de datetime tipo).

Conclusión

Entonces, lo que parece es que el método de conversión varchar sola dirección toma aproximadamente 1.8 μs y el método DateDiff sola dirección toma aproximadamente 0.18 μs. Estoy basando esto en el tiempo de "CPU base" más conservador en mis pruebas de 18458 ms en total para 25,920,000 filas, entonces 23218 ms / 25920000 = 0.18 μs. La aparente mejora de 10x parece mucho, pero francamente es 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 de entender.

Notas adicionales

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

Para aquellos 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) 9 de julio de 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition en Windows NT 5.2 (Build 3790: Service Pack 2)