Partilhar via

DATEDIFF_BIG (Transact-SQL)

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores Azure SQL Database AzureSQL Managed InstanceSQL database in Microsoft Fabric

Esta função retorna a contagem (como um valor inteiro grande assinado) dos limites de parte de data especificados cruzados entre a data de início e a data de término especificadas.

Consulte Tipos de dados e funções de data e hora (Transact-SQL) para obter uma visão geral de todos os tipos de dados e funções de data e hora do Transact-SQL.

Transact-SQL convenções de sintaxe

Sintaxe

DATEDIFF_BIG ( datepart , startdate , enddate )

Arguments

datepart
A parte de startdate e enddate que especifica o tipo de limite cruzado.

Observação

DATEDIFF_BIG não aceitará valores datepart de variáveis definidas pelo usuário ou como cadeias de caracteres entre aspas.

Esta tabela lista todos os nomes e abreviaturas válidos de argumentos datepart .

DataNome da parte abreviatura datepart
ano aaa, aaaa
trimestre QQ, Q
mês Mm, M
Dia do Ano Dy, Y
Dia DD, D
semana WK, WW
hora hh
minuto mi, n
segundo SS, S
milissegundos em
microssegundos MCS
nanossegundos NS

Observação

Cada nome de parte de data específico e abreviaturas para esse nome de parte de data retornará o mesmo valor.

data de início
Uma expressão que pode resolver para um dos seguintes valores:

  • date
  • datetime
  • datetimeoffset
  • datetime2
  • smalldatetime
  • time

Para data, DATEDIFF_BIG aceitará uma expressão de coluna, expressão, literal de cadeia de caracteres ou variável definida pelo usuário. Um valor literal de cadeia de caracteres deve ser resolvido para um datetime. Use anos de quatro dígitos para evitar problemas de ambiguidade. DATEDIFF_BIG subtrai startdate de enddate. Para evitar ambiguidades, use anos de quatro dígitos. Consulte Configurar a opção de configuração do servidor de corte de ano de dois dígitos para obter informações sobre anos de dois dígitos.

Data de fim
Consulte a data de início.

Tipo de Retorno

Assinado bigint

Valor de retorno

Devolve a grande diferença entre a data de início e a data de fim, expressa no limite definido por datepart.

Para um valor de retorno fora do intervalo para bigint (-9.223.372.036.854.775.808 a 9.223.372.036.854.775.807), DATEDIFF_BIG retorna um erro. Ao contrário do , que retorna um int e, portanto, pode transbordar um minuto ou mais, DATEDIFF_BIG só pode transbordar se usar precisão de nanossegundos onde a diferença entre a data final e a data de início é superior a 292 anos, 3 meses, 10 dias, 23 horas, 47 minutos e 16,8547758 segundos.

Se a data de início e a data de término receberem apenas um valor de tempo, e a parte de data não for uma parte de data de hora, DATEDIFF_BIG retornará 0.

DATEDIFF_BIG usa um componente de deslocamento de fuso horário de startdate ou enddate para calcular o valor de retorno.

Para um valor smalldatetime usado para startdate ou enddate, DATEDIFF_BIG sempre define segundos e milissegundos como 0 no valor de retorno porque smalldatetime só tem precisão para o minuto.

Se apenas um valor de tempo for atribuído a uma variável de tipo de dados de data, DATEDIFF_BIG definirá o valor da parte de data ausente como o valor padrão: 1900-01-01. Se apenas um valor de data for atribuído a uma variável de um tipo de dados de data ou hora, DATEDIFF_BIG definirá o valor da parte de tempo ausente como o valor padrão: 00:00:00. Se a data de início ou a data de término tiverem apenas uma parte de tempo e a outra apenas uma parte de data, DATEDIFF_BIG definirá as partes de data e hora ausentes para os valores padrão.

Se a data de início e a data de término tiverem tipos de dados de data diferentes, e uma tiver mais partes de tempo ou precisão de segundos fracionários do que a outra, DATEDIFF_BIG defina as partes ausentes da outra como 0.

Limites da parte de data

As instruções a seguir têm os mesmos valores startdate e enddate . Essas datas são adjacentes e diferem no tempo em cem nanossegundos (.0000001 segundo). A diferença entre a data de início e a data de término em cada instrução cruza um limite de calendário ou hora de sua parte de data. Cada instrução retorna 1. Se a data de início e a data de término tiverem valores de ano diferentes, mas tiverem os mesmos valores de semana do calendário, DATEDIFF_BIG retornarão 0 para a semana da parte da data.

SELECT DATEDIFF_BIG(year,        '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(quarter,     '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(month,       '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(dayofyear,   '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(day,         '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(week,        '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(hour,        '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(minute,      '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(second,      '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(millisecond, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');

Observações

Use DATEDIFF_BIG nas cláusulas , SELECT <list>, WHEREHAVING , e GROUP BY .ORDER BY

DATEDIFF_BIG implicitamente converte literais de cadeia de caracteres como um tipo datetime2 . Isso significa que DATEDIFF_BIG não suporta o formato YDM quando a data é passada como uma cadeia de caracteres. Você deve converter explicitamente a cadeia de caracteres para um tipo datetime ou smalldatetime para usar o formato YDM.

A especificação SET DATEFIRST não tem efeito sobre DATEDIFF_BIGo . DATEDIFF_BIG sempre usa o domingo como o primeiro dia da semana para garantir que a função opere de forma determinística.

DATEDIFF_BIG pode transbordar com um nanossegundo se a diferença entre enddate e startdate retornar um valor que está fora do intervalo para bigint.

Examples

Especificando colunas para data de início e data de término

Este exemplo usa diferentes tipos de expressões como argumentos para os parâmetros startdate e enddate . Ele calcula o número de limites de dias cruzados entre datas em duas colunas de uma tabela.

CREATE TABLE dbo.Duration  
    (startDate datetime2, endDate datetime2);  
    
INSERT INTO dbo.Duration(startDate,endDate)  
    VALUES('2007-05-06 12:10:09', '2007-05-07 12:10:09');  
    
SELECT DATEDIFF_BIG(day, startDate, endDate) AS 'Duration'  
    FROM dbo.Duration;  
-- Returns: 1

Localizando a diferença entre startdate e enddate como cadeias de caracteres de partes de data

DECLARE @date1 DATETIME2, @date2 DATETIME2, @result VARCHAR(100)
DECLARE @years BIGINT, @months BIGINT, @days BIGINT, @hours BIGINT, @minutes BIGINT, @seconds BIGINT, @milliseconds BIGINT

SET @date1 = '0001-01-01 00:00:00.00000000'
SET @date2 = '2018-12-12 07:08:01.12345678'

SELECT @years = DATEDIFF(yy, @date1, @date2)
IF DATEADD(yy, -@years, @date2) < @date1 
SELECT @years = @years-1
SET @date2 = DATEADD(yy, -@years, @date2)

SELECT @months = DATEDIFF(mm, @date1, @date2)
IF DATEADD(mm, -@months, @date2) < @date1 
SELECT @months=@months-1
SET @date2= DATEADD(mm, -@months, @date2)

SELECT @days=DATEDIFF(dd, @date1, @date2)
IF DATEADD(dd, -@days, @date2) < @date1 
SELECT @days=@days-1
SET @date2= DATEADD(dd, -@days, @date2)

SELECT @hours=DATEDIFF(hh, @date1, @date2)
IF DATEADD(hh, -@hours, @date2) < @date1 
SELECT @hours=@hours-1
SET @date2= DATEADD(hh, -@hours, @date2)

SELECT @minutes=DATEDIFF(mi, @date1, @date2)
IF DATEADD(mi, -@minutes, @date2) < @date1 
SELECT @minutes=@minutes-1
SET @date2= DATEADD(mi, -@minutes, @date2)

SELECT @seconds=DATEDIFF(s, @date1, @date2)
IF DATEADD(s, -@seconds, @date2) < @date1 
SELECT @seconds=@seconds-1
SET @date2= DATEADD(s, -@seconds, @date2)

SELECT @milliseconds=DATEDIFF(ms, @date1, @date2)

SELECT @result= ISNULL(CAST(NULLIF(@years,0) AS VARCHAR(10)) + ' years,','')
     + ISNULL(' ' + CAST(NULLIF(@months,0) AS VARCHAR(10)) + ' months,','')    
     + ISNULL(' ' + CAST(NULLIF(@days,0) AS VARCHAR(10)) + ' days,','')
     + ISNULL(' ' + CAST(NULLIF(@hours,0) AS VARCHAR(10)) + ' hours,','')
     + ISNULL(' ' + CAST(@minutes AS VARCHAR(10)) + ' minutes and','')
     + ISNULL(' ' + CAST(@seconds AS VARCHAR(10)) 
          + CASE WHEN @milliseconds > 0 THEN '.' + CAST(@milliseconds AS VARCHAR(10)) 
               ELSE '' END 
          + ' seconds','')

SELECT @result

Aqui está o conjunto de resultados.

2017 years, 11 months, 11 days, 7 hours, 8 minutes and 1.123 seconds

Ver exemplos mais estreitamente relacionados em DATEDIFF (Transact-SQL).

Consulte também

CAST e CONVERT (Transact-SQL)
DATEDIFF (Transact-SQL)

  • Last updated on