Qual é a melhor maneira de truncar uma data no SQL Server?

Se eu tiver um valor de data como 2010-03-01 17:34:12.018

Qual é a maneira mais eficiente de transformar isso em 2010-03-01 00:00:00.000 ?

Como uma questão secundária, qual é a melhor maneira de emular a function TRUNC do Oracle, que permitirá truncar nos limites Ano, Trimestre, Mês, Semana, Dia, Hora, Minuto e Segundo?

Para arredondar para o próximo dia inteiro , há três abordagens em uso amplo. O primeiro usa datediff para encontrar o número de dias desde o 0 datetime. O 0 datetime corresponde a 1 de janeiro de 1900. Ao adicionar a diferença do dia à data de início, você arredondou para um dia inteiro;

 select dateadd(d, 0, datediff(d, 0, getdate())) 

O segundo método é baseado em texto: trunca a descrição do texto com varchar(10) , deixando apenas a parte da data:

 select convert(varchar(10),getdate(),111) 

O terceiro método usa o fato de que um datetime é realmente um ponto flutuante que representa o número de dias desde 1900. Portanto, arredondando-o para um número inteiro, por exemplo, usando floor , você obtém o início do dia:

 select cast(floor(cast(getdate() as float)) as datetime) 

Para responder à sua segunda pergunta, o início da semana é mais complicado. Uma maneira é subtrair o dia da semana:

 select dateadd(dd, 1 - datepart(dw, getdate()), getdate()) 

Isso retorna uma parte de tempo também, então você teria que combiná-lo com um dos methods de remoção de tempo para chegar à primeira data. Por exemplo, com @start_of_day como uma variável para legibilidade:

 declare @start_of_day datetime set @start_of_day = cast(floor(cast(getdate() as float)) as datetime) select dateadd(dd, 1 - datepart(dw, @start_of_day), @start_of_day) 

O início do ano, mês, hora e minuto ainda funcionam com a abordagem “diferença desde 1900”:

 select dateadd(yy, datediff(yy, 0, getdate()), 0) select dateadd(m, datediff(m, 0, getdate()), 0) select dateadd(hh, datediff(hh, 0, getdate()), 0) select dateadd(mi, datediff(mi, 0, getdate()), 0) 

O arredondamento por segundo requer uma abordagem diferente, uma vez que o número de segundos desde 0 gera um estouro. Uma maneira de contornar isso é usar o início do dia, em vez de 1900, como uma data de referência:

 declare @start_of_day datetime set @start_of_day = cast(floor(cast(getdate() as float)) as datetime) select dateadd(s, datediff(s, @start_of_day, getdate()), @start_of_day) 

Para arredondar por 5 minutos , ajuste o método de arredondamento de minutos. Pegue o quociente da diferença de minutos, por exemplo, usando /5*5 :

 select dateadd(mi, datediff(mi,0,getdate())/5*5, 0) 

Isso funciona por trimestres e meia hora também.

Se você estiver usando o SQL Server 2008, poderá usar o novo tipo de dados Date assim:

 select cast(getdate() as date) 

Se você ainda precisar que seu valor seja um tipo de dados DateTime , faça isso:

 select cast(cast(getdate() as date) as datetime) 

Um método que deve funcionar em todas as versões do SQL Server é:

 select cast(floor(cast(getdate() as float)) as datetime) 

Experimentar:

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

UPDATE: responda na segunda pergunta: por anos você poderia usar uma versão modificada da minha resposta:

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

por trimestre:

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

e assim por diante.

Eu verifiquei, até minutos – está tudo bem. Mas em segundos eu recebi uma mensagem de estouro:

Diferença de duas colunas datetime causou estouro no tempo de execução.

Mais uma atualização: dê uma olhada na seguinte resposta para a mesma pergunta

Isso é tarde, mas produzirá os resultados exatos solicitados no post. Eu também sinto que é muito mais intuitivo do que usar o dateadd, mas essa é a minha preferência.

 declare @SomeDate datetime = '2010-03-01 17:34:12.018' SELECT DATEFROMPARTS( YEAR(@SomeDate) ,MONTH(@SomeDate) ,'01' ) AS CUR_DATE_FROM_PARTS ,DATETIMEFROMPARTS( YEAR(@SomeDate) ,MONTH(@SomeDate) ,'01' --DAY(@SomeDate) ,'00' --DATEPART(HOUR,@SomeDate) ,'00' --DATEPART(MINUTE,@SomeDate) ,'00' --DATEPART(SECOND,@SomeDate) ,'00' --DATEPART(MILLISECOND,@SomeDate) ) AS CUR_DATETIME_FROM_PARTS ,@SomeDate AS CUR_DATETIME ,YEAR(@SomeDate) AS CUR_YEAR ,MONTH(@SomeDate) AS CUR_MONTH ,DAY(@SomeDate) AS CUR_DAY ,DATEPART(HOUR,@SomeDate) AS CUR_HOUR ,DATEPART(MINUTE,@SomeDate) AS CUR_MINUTE ,DATEPART(SECOND,@SomeDate) AS CUR_SECOND ,DATEPART(MILLISECOND,@SomeDate) AS CUR_MILLISECOND FROM Your_Table 

Data truncada: 2010-03-01

Truncado DateTime: 2010-03-01 00: 00: 00.000

Data e Hora: 2010-03-01 17: 34: 12.017