operaciones - El mejor enfoque para eliminar parte del tiempo de datetime en SQL Server

diferencia entre datetime vs smalldatetime sql server

Question

¿Qué método proporciona el mejor rendimiento al eliminar la parte de tiempo de un campo de fecha y hora en SQL Server?

a) select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)

o

b) select cast(convert(char(11), getdate(), 113) as datetime)

El segundo método envía unos cuantos bytes más de cualquier forma, pero puede que no sea tan importante como la velocidad de la conversión.

Ambos también parecen ser muy rápidos, pero puede haber una diferencia en la velocidad cuando se trata de cientos de miles o más filas.

Además, ¿es posible que haya métodos aún mejores para deshacerse de la parte del tiempo de una fecha y hora en SQL?

Answer 1

¡TENER CUIDADO!

¡Los métodos a) yb) NO siempre tienen la misma salida!

select DATEADD(dd, DATEDIFF(dd, 0, ''2013-12-31 23:59:59.999''), 0)

Salida: 2014-01-01 00:00:00.000

select cast(convert(char(11), ''2013-12-31 23:59:59.999'', 113) as datetime)

Salida: 2013-12-31 00:00:00.000

(Probado en MS SQL Server 2005 y 2008 R2)

EDITAR: De acuerdo con el comentario de Adam, esto no puede suceder si lee el valor de fecha de la tabla, pero puede suceder si proporciona su valor de fecha como un literal (ejemplo: como un parámetro de un procedimiento almacenado llamado a través de ADO.NET).

Answer 2

¿Qué hay de select cast(cast my_datetime_field as date) as datetime) ? Esto da como resultado la misma fecha, con la hora establecida en 00:00, pero evita cualquier conversión a texto y también evita cualquier redondeo numérico explícito.

Answer 3

Aquí hay otra respuesta, de otra pregunta duplicada:

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

Este método de número mágico funciona un poco más rápido que el método DATEADD. (Parece que ~ 10%)

El tiempo de CPU en varias rondas de un millón de registros:

DATEADD   MAGIC FLOAT
500       453
453       360
375       375
406       360

Pero tenga en cuenta que estos números son posiblemente irrelevantes porque ya son MUY rápidos. A menos que tuviera conjuntos de registros de 100,000 o más, ni siquiera podía obtener el Tiempo de CPU para leer por encima de cero.

Teniendo en cuenta el hecho de que DateAdd está diseñado para este propósito y es más sólido, yo diría que use DateAdd.

Answer 4

Aquí hice una función para eliminar algunas partes de un datetime para SQL Server. Uso:

• El primer parámetro es la fecha y hora que se eliminará.
• El segundo param es un char:
  • s: redondea a segundos; elimina milisegundos
  • m: redondea a minutos; elimina segundos y milisegundos
  • h: redondea a las horas; Quita minutos, segundos y milisegundos.
  • d: redondea a días; Elimina horas, minutos, segundos y milisegundos.
• Devuelve el nuevo datetime

create function dbo.uf_RoundDateTime(@dt as datetime, @part as char) returns datetime as begin if CHARINDEX( @part, ''smhd'',0) = 0 return @dt; return cast( Case @part when ''s'' then convert(varchar(19), @dt, 126) when ''m'' then convert(varchar(17), @dt, 126) + ''00'' when ''h'' then convert(varchar(14), @dt, 126) + ''00:00'' when ''d'' then convert(varchar(14), @dt, 112) end as datetime ) end

Answer 5

Creo que si te TSQL a TSQL estrictamente, esta es la forma más rápida de truncar el tiempo:

 select convert(datetime,convert(int,convert(float,[Modified])))

Encontré que este método de truncamiento es aproximadamente un 5% más rápido que el método DateAdd . Y esto se puede modificar fácilmente para redondear al día más cercano de esta manera:

select convert(datetime,ROUND(convert(float,[Modified]),0))

Answer 6

Creo que te refieres a cast(floor(cast(getdate()as float))as datetime)

Lo real es solo de 32 bits, y podría perder alguna información.

Este es el cast(cast(getdate()+x-0.5 as int)as datetime) más rápido cast(cast(getdate()+x-0.5 as int)as datetime)

... aunque solo un 10% más rápido (about 0.49 microseconds CPU vs. 0.58)

Esto fue recomendado y toma el mismo tiempo en mi prueba justo ahora: DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)

En SQL 2008, la función CLR de SQL es aproximadamente 5 veces más rápida de lo que sería usar una función de SQL, a 1,35 microsegundos frente a 6,5 ​​microsecciones, lo que indica una sobrecarga de la función mucho menor para una función de CLR de SQL frente a un simple UDF de SQL.

En SQL 2005, la función CLR de SQL es 16 veces más rápida, según mis pruebas, en comparación con esta función lenta:

create function dateonly (  @dt datetime )
returns datetime
as
begin
return cast(floor(cast(@dt as float))as int)
end

Answer 7

De verdad me gusta:

[date] = CONVERT(VARCHAR(10), GETDATE(), 120)

El código de formato 120 forzará la fecha en el estándar ISO 8601:

''YYYY-MM-DD'' or ''2017-01-09''

¡Super fácil de usar en dplyr ( R ) y pandas ( Python )!

Answer 8

Despeje el tiempo en las inserciones / actualizaciones en primer lugar. En cuanto a la conversión sobre la marcha, nada puede vencer a una función definida por el usuario en términos de mantenimiento:

select date_only(dd)

La implementación de date_only puede ser cualquier cosa que desee, ahora se retira y el código de llamada es mucho más limpio.

Answer 9

En SQL Server 2008, hay un tipo de datos DATE (también un tipo de datos TIME).

CAST(GetDate() as DATE)

o

declare @Dt as DATE = GetDate()

Answer 10

En SQL Server 2008, puede utilizar:

CONVERT(DATE, getdate(), 101)

Answer 11

En caso de que alguien esté buscando aquí una versión de Sybase ya que varias de las versiones anteriores no funcionaron

CAST(CONVERT(DATE,GETDATE(),103) AS DATETIME)

• Probado en I SQL v11 ejecutándose en Adaptive Server 15.7

Answer 12

Estrictamente, el método a requiere menos recursos:

a) select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)

Se ha comprobado que requiere menos uso de CPU para la misma duración total de un millón de filas por alguien con demasiado tiempo en sus manos: ¿ La forma más eficiente en SQL Server para obtener la fecha de la fecha y la hora?

Vi una prueba similar en otros lugares con resultados similares también.

Prefiero el DATEADD / DATEDIFF porque:

• varchar está sujeto a problemas de idioma / fecha y formato
  Ejemplo: ¿Por qué mi expresión CASE no es determinista?
• float se basa en el almacenamiento interno
• se extiende para trabajar el primer día del mes, mañana, etc. cambiando la base "0"

Edición, octubre 2011

Para SQL Server 2008+, puede CAST hasta la date . O simplemente use la date así que no hay tiempo para eliminar.

Editar, enero 2012

Un ejemplo práctico de cuán flexible es esto: necesidad de calcular por hora redondeada o cifra de fecha en el servidor SQL

Edición, mayo 2012

No use esto en las cláusulas WHERE y similares sin pensar: agregar una función o CAST a una columna invalida el uso del índice. Consulte el número 2 aquí: http://www.simple-talk.com/sql/t-sql-programming/ten-common-sql-programming-mistakes/

Ahora, esto tiene un ejemplo de las últimas versiones del optimizador de SQL Server que administra CAST hasta la fecha correctamente, pero en general será una mala idea ...

Edición, septiembre de 2018, para datetime2

DECLARE @datetime2value datetime2 = ''02180912 11:45'' --this is deliberately within datetime2, year 0218
DECLARE @datetime2epoch datetime2 = ''19000101''

select DATEADD(dd, DATEDIFF(dd, @datetime2epoch, @datetime2value), @datetime2epoch)

Answer 13

Para mí el código de abajo siempre es un ganador:

SELECT CONVERT(DATETIME, FLOOR(CONVERT(FLOAT,GETDATE())));

Answer 14

Por supuesto, esto es un hilo viejo, pero para hacerlo completo.

Desde SQL 2008 puede usar el tipo de datos DATE por lo que simplemente puede hacer:

SELECT CONVERT(DATE,GETDATE())

Answer 15

Si es posible, para cosas especiales como esta, me gusta usar las funciones CLR.

En este caso:

[Microsoft.SqlServer.Server.SqlFunction]
    public static SqlDateTime DateOnly(SqlDateTime input)
    {
        if (!input.IsNull)
        {
            SqlDateTime dt = new SqlDateTime(input.Value.Year, input.Value.Month, input.Value.Day, 0, 0, 0);

            return dt;
        }
        else
            return SqlDateTime.Null;
    }

Answer 16

Vea esta pregunta:
¿Cómo puedo truncar un datetime en SQL Server?

Hagas lo que hagas, no uses el método de cuerdas . Esa es la peor manera en que puedes hacerlo.

Answer 17

Ya respondí, pero también lo tiraré por ahí ... esto supuestamente también se realiza bien pero funciona tirando el decimal (que almacena el tiempo) del flotador y devolviendo solo la parte entera (que es la fecha)

 CAST(
FLOOR( CAST( GETDATE() AS FLOAT ) )
AS DATETIME
)

La segunda vez que encontré esta solución ... tomé este código.

Answer 18

Yo usaría:

CAST
(
CAST(YEAR(DATEFIELD) as varchar(4)) + ''/'' CAST(MM(DATEFIELD) as varchar(2)) + ''/'' CAST(DD(DATEFIELD) as varchar(2)) as datetime
) 

De este modo, crea efectivamente un nuevo campo a partir del campo de fecha que ya tiene.

Answer 19

Yo, personalmente, casi siempre uso funciones definidas por el usuario para esto si se trata de SQL Server 2005 (o una versión inferior), sin embargo, debe tenerse en cuenta que existen inconvenientes específicos al usar UDF, especialmente si se aplican a las cláusulas WHERE (ver más abajo y los comentarios sobre esta respuesta para más detalles). Si utiliza SQL Server 2008 (o superior), consulte a continuación.

De hecho, para la mayoría de las bases de datos que creo, agrego estos UDF casi al comienzo, ya que sé que hay un 99% de probabilidad de que los necesite tarde o temprano.

Creo uno para "solo fecha" y "solo tiempo" (aunque el "solo fecha" es, con mucho, el más usado de los dos).

Aquí hay algunos enlaces a una variedad de UDF relacionados con la fecha:

Funciones esenciales de fecha, hora y fecha y hora del servidor SQL
Obtener la función de solo fecha

El último enlace muestra nada menos que 3 formas diferentes de obtener la fecha solo parte de un campo de fecha y hora y menciona algunas ventajas y desventajas de cada enfoque.

Si usa un UDF, debe tener en cuenta que debe intentar evitar usar el UDF como parte de una cláusula WHERE en una consulta, ya que esto dificultará en gran medida el rendimiento de la consulta. La razón principal de esto es que el uso de un UDF en una cláusula WHERE hace que esa cláusula non-sargable , lo que significa que SQL Server ya no puede usar un índice con esa cláusula para mejorar la velocidad de ejecución de consultas. Con referencia a mi propio uso de UDF, frecuentemente usaré la columna de fecha "sin procesar" dentro de la cláusula WHERE, pero aplicaré el UDF a la columna SELECCIONADA. De esta manera, el UDF solo se aplica al conjunto de resultados filtrado y no a todas las filas de la tabla como parte del filtro.

Por supuesto, el mejor enfoque para esto es utilizar SQL Server 2008 (o superior) y separar las fechas y horas , ya que el motor de la base de datos de SQL Server proporciona de forma nativa los componentes de fecha y hora individuales, y puede consultarlos de manera eficiente. sin la necesidad de un UDF u otro mecanismo para extraer la parte de fecha u hora de un tipo de fecha y hora compuesto.

Answer 20

seleccione CONVERTIR (char (10), GetDate (), 126)

Answer 21

CAST(round(cast(getdate()as real),0,1) AS datetime)

Este método no utiliza la función de cadena. Date es básicamente un tipo de datos real con dígitos antes del decimal que es fracción de un día.

Supongo que esto será más rápido que mucho.

Answer 22

SELECT CAST(CAST(GETDATE() AS DATE) AS DATETIME)

Answer 23

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