DATEDIFF_BIG (Transact-SQL)
Aplica-se a: 
SQL Server 2016 (13.x) e posterior Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure
Essa função retorna a contagem (como um grande valor inteiro com sinal) dos limites de datepart especificados cruzados entre os parâmetros especificados startdate e enddate.
Confira Funções e tipos de dados de data e hora (Transact-SQL) para ter uma visão geral de todas as funções e tipos de dados de data e hora do Transact-SQL.
Convenções de sintaxe de Transact-SQL
Sintaxe
DATEDIFF_BIG ( datepart , startdate , enddate )
Argumentos
datepart
A parte de startdate e de enddate que especifica o tipo de limite ultrapassado.
Observação
DATEDIFF_BIG não aceitará valores de datepart de variáveis definidas pelo usuário ou como cadeias de caracteres entre aspas.
Esta tabela lista todos os nomes e abreviações de argumentos datepart válidos.
| Nome do datepart | Abreviação do datepart |
|---|---|
| year | yy, yyyy |
| quarter | qq, q |
| month | mm, m |
| dayofyear | dy, y |
| day | dd, d |
| week | wk, ww |
| hour | hh |
| minute | mi, n |
| second | ss, s |
| millisecond | ms |
| microsecond | mcs |
| nanosecond | ns |
Observação
Cada nome e abreviação de datepart específico para esse nome de datepart retornará um mesmo valor.
startdate
Uma expressão que pode resolver um dos seguintes valores:
- date
- datetime
- datetimeoffset
- datetime2
- smalldatetime
- time
Para date, DATEDIFF_BIG aceitará uma variável de expressão de coluna, de expressão, de literal de cadeia de caracteres ou definida pelo usuário. Um valor literal de cadeia de caracteres deve resolver um datetime. Para evitar ambiguidade, use anos de quatro dígitos. DATEDIFF_BIG subtrai startdate de enddate. Para evitar ambiguidade, use anos de quatro dígitos. Consulte Configurar a opção two digit year cutoff de configuração de servidor para obter informações sobre anos de dois dígitos.
enddate
Consulte startdate.
Tipo de retorno
bigint com sinal
Valor retornado
Retorna a diferença bigint entre startdate e enddate expressa no limite definido por datepart.
Para um valor retornado 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 de, que retorna um int e, portanto, pode estourar um minuto ou mais, o DATEDIFF_BIG só poderá estourar se estiver usando uma precisão de nanossegundos, em que a diferença entre enddate e startdate é superior a 292 anos, 3 meses, 10 dias, 23 horas, 47 minutos e 16,8547758 segundos.
Se startdate e enddate receberem apenas um valor temporal e datepart não for um datepart de hora, DATEDIFF_BIG retornará 0.
DATEDIFF_BIG não usa um componente de diferença de fuso horário de startdate ou enddate para calcular o valor retornado.
Para um valor smalldatetime usado para startdate ou para enddate, DATEDIFF_BIG sempre define segundos e milissegundos como 0 no valor retornado, porque smalldatetime tem apenas a precisão do minuto.
Se apenas um valor temporal 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 hora ausente como o valor padrão: 00:00:00. Se startdate ou enddate tiver apenas uma parte de hora e a outra apenas uma parte de data, DATEDIFF_BIG definirá as partes de hora e data ausentes como os valores padrão.
Se startdate e enddate tiverem diferentes tipos de dados de data e um tiver mais partes de hora ou precisão de segundos fracionários do que o outro, DATEDIFF_BIG definirá as partes ausentes do outro como 0.
Limites de datepart
As instruções a seguir têm os mesmos valores de startdate e de enddate. Essas datas são adjacentes e diferem no tempo em 100 nanossegundos (0,0000001 segundo). A diferença entre startdate e enddate em cada instrução cruza um calendário ou limite de hora de sua datepart. Cada instrução retorna 1. Se startdate e enddate tiverem diferentes valores de ano, mas os mesmos valores de semana do calendário, DATEDIFF_BIG retornará 0 para datepart week.
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');
Comentários
Use DATEDIFF_BIG nas cláusulas SELECT <list>, WHERE, HAVING, GROUP BY e ORDER BY.
DATEDIFF_BIG converte implicitamente literais de cadeias de caracteres como um tipo datetime2. Isso significa que DATEDIFF_BIG não dá suporte ao formato YDM quando a data é transmitida como uma cadeia de caracteres. É necessário converter explicitamente a cadeia de caracteres em um tipo de datetime ou smalldatetime para usar o formato YDM.
Especificar SET DATEFIRST não tem efeito sobre DATEDIFF_BIG. DATEDIFF_BIG sempre usa domingo como o primeiro dia da semana para garantir que a função opere de maneira determinística.
DATEDIFF_BIG poderá estourar um nanossegundo se a diferença entre enddate e startdate retornar um valor fora do intervalo para bigint.
Exemplos
Especificando colunas para startdate e enddate
Este exemplos usa diferentes tipos de expressões como argumentos para os parâmetros startdate e enddate. Ele calcula o número de limites de dia 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
Veja a seguir o conjunto de resultados.
2017 years, 11 months, 11 days, 7 hours, 8 minutes and 1.123 seconds
Veja mais detalhadamente os exemplos relacionados em DATEDIFF (Transact-SQL).