Melhor abordagem para remover parte do tempo de datetime no SQL Server

Qual método fornece o melhor desempenho ao remover a parte de tempo de um campo de data e hora no SQL Server?

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

ou

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

O segundo método envia mais alguns bytes de qualquer forma, mas isso pode não ser tão importante quanto a velocidade da conversão.

Ambos também parecem ser muito rápidos, mas pode haver uma diferença de velocidade ao lidar com centenas de milhares ou mais linhas?

Além disso, é possível que existam methods ainda melhores para se livrar da parte do tempo de um datetime no SQL?

Estritamente, o método a é o menos intensivo em resources:

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

Comprovado menos CPU intensivo para a mesma duração total um milhão de linhas por alguém com muito tempo em suas mãos: maneira mais eficiente no SQL Server para obter data de data + hora?

Eu vi um teste semelhante em outro lugar com resultados semelhantes também.

Eu prefiro o DATEADD / DATEDIFF porque:

  • varchar está sujeito a problemas de formato de linguagem / data
    Exemplo: Por que minha expressão CASE é não determinística?
  • float depende de armazenamento interno
  • ele se estende para trabalhar primeiro dia do mês, amanhã, etc, alterando a base “0”

Editar, outubro de 2011

Para o SQL Server 2008+, você pode CAST até o date . Ou apenas use a date então não há tempo para remover.

Editar, janeiro de 2012

Um exemplo trabalhado de como isso é flexível: É necessário calcular por hora arredondada ou figura de data no sql server

Editar, maio de 2012

Não use isso em cláusulas WHERE e similares sem pensar: adicionar uma function ou CAST a uma coluna invalida o uso do índice. Veja o número 2 aqui: http://www.simple-talk.com/sql/t-sql-programming/ten-common-sql-programming-mistakes/

Agora, isso tem um exemplo de versões mais recentes do otimizador do SQL Server gerenciando o CAST até a data corretamente, mas geralmente será uma má idéia …

No SQL Server 2008, você pode usar:

 CONVERT(DATE, getdate(), 101) 

Claro que este é um segmento antigo, mas para torná-lo completo.

A partir do SQL 2008, você pode usar o tipo de dados DATE para simplesmente fazer:

 SELECT CONVERT(DATE,GETDATE()) 
 SELECT CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME) 

No SQL Server 2008, há um tipo de dados DATE (também um tipo de dados TIME).

 CAST(GetDate() as DATE) 

ou

 declare @Dt as DATE = GetDate() 

Aqui está outra resposta, de outra pergunta duplicada:

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

Este método de número mágico executa um pouco mais rápido que o método DATEADD. (Parece ~ 10%)

O tempo de CPU em várias rodadas de um milhão de registros:

 DATEADD MAGIC FLOAT 500 453 453 360 375 375 406 360 

Mas note que esses números são possivelmente irrelevantes, porque eles já são muito rápidos. A menos que eu tivesse conjuntos de registros de 100.000 ou mais, eu não conseguiria nem o Tempo de CPU para ler acima de zero.

Considerando o fato de que DateAdd é feito para essa finalidade e é mais robusto, eu diria que use DateAdd.

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

Faixa de tempo em inserções / atualizações em primeiro lugar. Quanto à conversão on-the-fly, nada pode bater uma function definida pelo usuário em termos de manutenção:

 select date_only(dd) 

A implementação de date_only pode ser o que você quiser – agora é abstraída e o código de chamada é muito mais limpo.

Veja esta pergunta:
Como posso truncar um datetime no SQL Server?

Faça o que fizer, não use o método string . Essa é a pior maneira de fazer isso.

Já respondi, mas também joguei isso lá fora … isso, supostamente, também preforma bem, mas funciona descartando o decimal (que armazena o tempo) do float e retornando apenas a parte inteira (que é a data).

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

segunda vez eu encontrei esta solução … eu peguei esse código

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

Este método não usa a function string. Date é basicamente um tipo de dados real com dígitos antes do decimal serem fração de um dia.

isso eu acho que vai ser mais rápido que muito.

Para mim, o código abaixo é sempre um vencedor:

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

CUIDADO!

O método a) eb) não tem sempre a mesma saída!

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

Produção: 2014-01-01 00:00:00.000

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

Produção: 2013-12-31 00:00:00.000

(Testado no MS SQL Server 2005 e 2008 R2)

EDIT: De acordo com o comentário de Adam, isso não pode acontecer se você ler o valor de data da tabela, mas isso pode acontecer se você fornecer seu valor de data como um literal (exemplo: como um parâmetro de um procedimento armazenado chamado via ADO.NET).

selecione CONVERT (char (10), GetDate (), 126)

Eu acho que você quer dizer cast(floor(cast(getdate()as float))as datetime)

real tem apenas 32 bits e pode perder algumas informações

Este é o cast(cast(getdate()+x-0.5 as int)as datetime) mais rápido cast(cast(getdate()+x-0.5 as int)as datetime)

… embora apenas cerca de 10% mais rápido (about 0.49 microseconds CPU vs. 0.58)

Isso foi recomendado e leva o mesmo tempo no meu teste agora: DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)

No SQL 2008, a function SQL CLR é cerca de 5 vezes mais rápida do que usar uma function SQL em 1,35 microssegundos versus 6,5 microssegundos, indicando uma sobrecarga de chamada de function muito menor para uma function SQL CLR versus uma simples UDF SQL.

No SQL 2005, a function SQL CLR é 16 vezes mais rápida, por meu teste, em comparação com essa function lenta:

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

Que tal select cast(cast my_datetime_field as date) as datetime) ? Isso resulta na mesma data, com a hora definida como 00:00, mas evita qualquer conversão para texto e também evita qualquer arredondamento numérico explícito.

Eu acho que se você ficar estritamente com TSQL que esta é a maneira mais rápida de truncar o tempo:

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

Eu encontrei este método de truncamento para ser cerca de 5% mais rápido que o método DateAdd . E isso pode ser facilmente modificado para arredondar para o dia mais próximo assim:

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

Aqui eu fiz uma function para remover algumas partes de um datetime para o SQL Server. Uso:

  • Primeiro param é o datetime a ser retirado.
  • Segundo param é um char:
    • s: arredonda para segundos; remove milissegundos
    • m: arredonda para minutos; remove segundos e milissegundos
    • h: arredonda para horas; remove minutos, segundos e milissegundos.
    • d: arredonda para dias; remove horas, minutos, segundos e milissegundos.
  • Retorna o novo 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

Apenas no caso de alguém estar procurando por uma versão do Sybase, já que várias das versões acima não funcionaram

 CAST(CONVERT(DATE,GETDATE(),103) AS DATETIME) 
  • Testado no I SQL v11 em execução no Adaptive Server 15.7

Eu realmente gosto:

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

O código de formato 120 coagirá a data no padrão ISO 8601:

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

Super fácil de usar em dplyr ( R ) e pandas ( Python )!

Se possível, para coisas especiais como essa, eu gosto de usar as funções do CLR.

Nesse 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; } 

Eu, pessoalmente, quase sempre uso funções Definidas pelo Usuário para isso se estiver lidando com o SQL Server 2005 (ou versão inferior), no entanto, deve-se notar que há desvantagens específicas no uso de UDFs, especialmente se forem aplicadas a cláusulas WHERE (veja abaixo e os comentários sobre esta resposta para mais detalhes). Se estiver usando o SQL Server 2008 (ou superior) – veja abaixo.

Na verdade, para a maioria dos bancos de dados que eu crio, adiciono essas UDFs logo no início, pois sei que há 99% de chance de precisar delas mais cedo ou mais tarde.

Eu crio um para “somente data” e “somente tempo” (embora o “somente data” seja de longe o mais usado dos dois).

Aqui estão alguns links para uma variedade de UDFs relacionados a datas:

Funções Essenciais de Data, Hora e DateTime do SQL Server
Obter function de data única

Esse último link mostra não menos que 3 maneiras diferentes de obter a data apenas parte de um campo de data e hora e menciona alguns prós e contras de cada abordagem.

Se estiver usando uma UDF, deve-se observar que você deve tentar evitar o uso da UDF como parte de uma cláusula WHERE em uma consulta, pois isso prejudicará bastante o desempenho da consulta. A principal razão para isso é que usar uma UDF em uma cláusula WHERE renderiza essa cláusula como não sargable , o que significa que o SQL Server não pode mais usar um índice com essa cláusula para melhorar a velocidade de execução da consulta. Com referência ao meu próprio uso de UDFs, usarei com frequência a coluna de data “bruta” dentro da cláusula WHERE, mas aplique a UDF à coluna SELECTed. Dessa forma, a UDF é aplicada apenas ao conjunto de resultados filtrados e não a todas as linhas da tabela como parte do filtro.

Obviamente, a melhor abordagem absoluta para isso é usar o SQL Server 2008 (ou superior) e separar suas datas e horas , pois o mecanismo de database do SQL Server fornece nativamente os componentes individuais de data e hora e pode consultá-los de forma eficiente sem a necessidade de um UDF ou outro mecanismo para extrair a parte de data ou hora de um tipo de datetime composto.

Eu usaria:

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

Assim, efetivamente criando um novo campo a partir do campo de data que você já tem.