Como remover a parte do tempo de um valor de data e hora (SQL Server)?

votos
77

Aqui está o que eu uso:

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

Estou pensando que pode haver uma maneira melhor e mais elegante.

requisitos:

  • Tem que ser o mais rápido possível (menos de elenco, melhor).
  • O resultado final tem de ser um datetimetipo, não uma string.
Publicado 05/08/2008 em 21:08
fonte usuário
Em outras línguas...                            


6 respostas

votos
106

SQL Server 2008 e até

No SQL Server 2008 e, é claro o caminho mais rápido é Convert(date, @date). Este pode ser convertido de volta a um datetimeou datetime2, se necessário.

O que é realmente melhor em SQL Server 2005 e mais velhos?

Eu vi reivindicações inconsistentes sobre o que é mais rápido para truncar o tempo a partir de uma data no SQL Server, e algumas pessoas até mesmo disse que eles fizeram testes, mas minha experiência tem sido diferente. Então, vamos fazer alguns testes mais rigorosos e deixar todo mundo tem o script assim que se eu cometer erros as pessoas podem me corrigir.

Conversões flutuador não são precisos

Em primeiro lugar, gostaria de ficar longe de converter datetimea float, porque não converter corretamente. Você pode se safar fazendo a coisa do tempo de remoção com precisão, mas eu acho que é uma má idéia para usá-lo porque ele se comunica de forma implícita para os desenvolvedores que esta é uma operação segura e não é . Dê uma olhada:

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

Isso não é algo que deve ser ensinar as pessoas em nosso código ou em nossos exemplos online.

Além disso, não é ainda a maneira mais rápida!

Prova - Teste de Desempenho

Se você quiser realizar alguns testes si mesmo para ver como os diferentes métodos realmente empilhar, então você precisa este script de instalação para executar os testes mais abaixo:

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

Por favor note que este cria uma tabela 427,57 MB em seu banco de dados e terá algo como 15-30 minutos para ser executado. Se seu banco de dados é pequeno e definido para crescimento de 10% vai demorar mais tempo do que se você tamanho grande o suficiente em primeiro lugar.

Agora para o script de teste de desempenho real. Por favor, note que é proposital para não retornar as linhas de volta ao cliente, pois isso é uma loucura caro em 26 milhões de linhas e iria esconder as diferenças de desempenho entre os métodos.

resultados de desempenho

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;

Alguns Análise Rambling

Algumas notas sobre isso. Primeiro de tudo, se apenas realizando um GROUP BY ou uma comparação, não há necessidade de converter de volta para datetime. Assim, você pode poupar algum CPU, evitando que, a menos que você precisa o valor final para fins de exibição. Você pode até mesmo GROUP BY o valor não convertido e colocar a conversão somente na 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)

Além disso, ver como as conversões numéricas só ter um pouco mais de tempo para converter de volta para datetime, mas a varcharconversão é quase o dobro? Isso revela a parte da CPU que é dedicado ao cálculo de data nas consultas. Há partes do uso da CPU que não envolvem cálculo de data, e este parece ser algo perto de 19875 ms nas consultas acima. Em seguida, a conversão tiver alguma quantidade adicional, portanto, se existem duas conversões, essa quantidade é utilizada até aproximadamente duas vezes.

Mais exame revela que em comparação com Convert(, 112)a Convert(, 101)consulta tem alguma despesa adicional de CPU (uma vez que utiliza um longo varchar?), Porque a segunda conversão de volta para datenão custar tanto quanto a conversão inicial para varchar, mas com Convert(, 112)ele está mais perto do mesmo 20000 custo base ms CPU.

Aqui estão esses cálculos sobre o tempo de CPU que eu usei para a análise acima:

     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
  • rodada é o tempo de CPU para uma viagem de ida de volta para datetime.

  • único é o tempo da CPU para uma única conversão para o tipo de dados alternativo (o que tem o efeito colateral de remover a parte do tempo).

  • de base é o cálculo de subtraindo singlea diferença entre as duas invocações: single - (round - single). É um valor aproximado que assume a conversão para e a partir de que tipo de dados e datetimeé, aproximadamente, o mesmo em qualquer direcção. Parece que este pressuposto não é perfeito, mas está perto, porque os valores estão todos perto de 20000 ms com uma única exceção.

Uma coisa mais interessante é que o custo base é quase igual ao único Convert(date)método (que tem de ser quase 0 custo, como o servidor pode extrair internamente a parte do dia inteiro para a direita fora dos quatro primeiros bytes do datetimetipo de dados).

Conclusão

Então, o que parece é que a direção único varcharmétodo de conversão demora cerca de 1.8 ms e o sentido único DateDiffmétodo leva cerca de 0,18 mS. Eu estou baseando isso no momento mais conservador "CPU base" no meu teste de 18458 ms total para 25,920,000 linhas, assim 23218 ms / 25920000 = 0,18 mS. A aparente 10x melhoria parece muito, mas é francamente muito pequeno até que você está lidando com centenas de milhares de linhas (617k linhas = 1 segundo de poupança).

Mesmo com esta pequena melhoria absoluta, na minha opinião, o DateAddmétodo ganha porque é a melhor combinação de desempenho e clareza. A resposta que exige um "número mágico" de 0.50000004que vai morder alguém algum dia (cinco zeros ou seis ???), além de que é mais difícil de entender.

Notas Adicionais

Quando eu chegar em algum tempo eu vou mudar 0.50000004para '12:00:00.003'e ver como ele faz. Ele é convertido para o mesmo datetimevalor e acho que é muito mais fácil de lembrar.

Para os interessados, os testes acima foram executados em um servidor onde @@ versão retorna o seguinte:

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 09 de julho de 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition no Windows NT 5.2 (Build 3790: Service Pack 2)

Respondeu 12/09/2010 em 23:57
fonte usuário

votos
27

SQL Server 2008 tem uma nova data tipo de dados e isso simplifica este problema:

SELECT CAST(CAST(GETDATE() AS date) AS datetime)
Respondeu 06/08/2008 em 07:44
fonte usuário

votos
16

Itzik Ben-Gan em Cálculos DATETIME, Parte 1 (SQL Server Magazine, fevereiro de 2007) mostra três métodos de realizar tal conversão ( mais lento para o mais rápido , a diferença entre o segundo e terceiro método é pequeno):

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)

Sua técnica (lançando a flutuar ) é sugerido por um leitor na edição de abril da revista. De acordo com ele, tem um desempenho comparável ao da segunda técnica apresentada acima.

Respondeu 06/08/2008 em 09:06
fonte usuário

votos
11

Seu CAST- FLOOR- CASTjá parece ser a melhor forma, pelo menos no MS SQL Server 2005.

Algumas outras soluções que eu vi tem uma seqüência de conversão, como Select Convert(varchar(11), getdate(),101)neles, que é mais lento por um fator de 10.

Respondeu 05/08/2008 em 21:12
fonte usuário

votos
3

Tente por favor:

SELECT CONVERT(VARCHAR(10),[YOUR COLUMN NAME],105) [YOURTABLENAME]
Respondeu 29/06/2013 em 10:49
fonte usuário

votos
0

SQL2005: Eu recomendo lançado em vez de dateadd. Por exemplo,

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

em média cerca de 10% mais rápido no meu conjunto de dados, que

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

(E fundição em smalldatetime foi mais rápido ainda)

Respondeu 05/11/2014 em 04:26
fonte usuário

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