DATEDIFF (Transact-SQL)

Artigo
04/26/2023

Aplica-se a:SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure Azure Synapse Analytics PDW (Analytics Platform System)

Essa função retorna a contagem (como um valor inteiro com sinal) dos limites de datepart especificados cruzados entre os parâmetros especificados startdate e enddate.

Confira DATEDIFF_BIG (Transact-SQL) para obter uma função que manipula diferenças maiores entre os valores 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 ( datepart , startdate , enddate )

Observação

Para exibir a sintaxe do Transact-SQL para o SQL Server 2014 (12.x) e versões anteriores, confira a Documentação das versões anteriores.

Argumentos

datepart
As unidades em que DATEDIFF relata a diferença entre startdate e enddate. Unidades de datepart usadas com frequência incluem month ou second.

O valor de datepart não pode ser especificado em uma variável, nem como uma cadeia de caracteres entre aspas, como 'month'.

A tabela a seguir lista todos os valores válidos de datepart. DATEDIFF aceita o nome completo de datepart ou qualquer abreviação listada do nome completo.

Nome do datepart	Abreviação do datepart
year	y, yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy
day	dd, d
week	wk, ww
weekday	dw, w
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

Use anos de quatro dígitos para evitar ambiguidade. Consulte Configurar a opção two digit year cutoff de configuração de servidor para obter informações sobre valores de anos de dois dígitos.

enddate
Consulte startdate.

Tipo de retorno

int

Valor retornado

A diferença int entre startdate e enddate expressa no limite definido por datepart.

Por exemplo, SELECT DATEDIFF(day, '2036-03-01', '2036-02-28'); retorna-2, indicando que 2036 deve ser um ano bissexto. Esse caso significa que, se começarmos com startdate '2036-03-01' e, em seguida, contarmos -2 dias, chegaremos ao enddate '2036-02-28'.

Para um valor retornado fora do intervalo para int (-2.147.483.648 a +2.147.483.647), DATEDIFF retorna um erro. Para millisecond, a diferença máxima entre startdate e enddate é de 24 dias, 20 horas, 31 minutos e 23.647 segundos. Para segundo, a diferença máxima é de 68 anos, 19 dias, 3 horas, 14 minutos e 7 segundos.

Se startdate e enddate receberem apenas um valor temporal e datepart não for um datepart de hora, DATEDIFF retornará 0.

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

Como smalldatetime tem precisão apenas quanto ao minuto, segundos e milissegundos são sempre definidos como 0 no valor retornado quando um valor smalldatetime é usado para startdate ou enddate.

Se apenas um valor temporal for atribuído a uma variável de tipo de dados de data, DATEDIFF 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 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 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 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.

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

Se startdate e enddate tiverem diferentes valores de ano, mas os mesmos valores de semana do calendário, DATEDIFF retornará 0 para datepartweek.

Comentários

Use DATEDIFF nas cláusulas SELECT <list>, WHERE, HAVING, GROUP BY e ORDER BY.

DATEDIFF converte implicitamente literais de cadeias de caracteres como um tipo datetime2. Isso significa que DATEDIFF não é compatível com o formato YDM quando a data é transmitida como 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. DATEDIFF sempre usa domingo como o primeiro dia da semana para garantir que a função opere de maneira determinística.

DATEDIFF poderá estourar com uma precisão de minuto ou mais se a diferença entre enddate e startdate retornar um valor fora do intervalo para int.

Exemplos

Esses exemplos usam diferentes tipos de expressões como argumentos para os parâmetros startdate e enddate.

a. Especificando colunas para startdate e enddate

Este exemplo 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(day, startDate, endDate) AS 'Duration'  
    FROM dbo.Duration;  
-- Returns: 1

B. Especificando variáveis definidas pelo usuário para startdate e enddate

Nesse exemplo, variáveis definidas pelo usuário funcionam como argumentos para startdate e enddate.

DECLARE @startdate DATETIME2 = '2007-05-05 12:10:09.3312722';  
DECLARE @enddate   DATETIME2 = '2007-05-04 12:10:09.3312722';   
SELECT DATEDIFF(day, @startdate, @enddate);

C. Especificando funções de sistema escalares para startdate e enddate

Esse exemplo usa funções do sistema escalares como argumentos para startdate e enddate.

SELECT DATEDIFF(millisecond, GETDATE(), SYSDATETIME());

D. Especificando subconsultas e funções escalares para startdate e enddate

Este exemplo usa subconsultas e funções escalares como argumentos para startdate e enddate.

USE AdventureWorks2022;  
GO  
SELECT DATEDIFF(day,
    (SELECT MIN(OrderDate) FROM Sales.SalesOrderHeader),  
    (SELECT MAX(OrderDate) FROM Sales.SalesOrderHeader));

E. Especificando constantes para startdate e enddate

Este exemplo usa constantes de caractere como argumentos para startdate e enddate.

SELECT DATEDIFF(day,
   '2007-05-07 09:53:01.0376635',
   '2007-05-08 09:53:01.0376635');

F. Especificando expressões numéricas e funções de sistema escalares para enddate

Esse exemplo usa uma expressão numérica, (GETDATE() + 1), e as funções do sistema escalares, GETDATE e SYSDATETIME, como argumentos para enddate.

USE AdventureWorks2022;  
GO  
SELECT DATEDIFF(day, '2007-05-07 09:53:01.0376635', GETDATE() + 1)
    AS NumberOfDays  
    FROM Sales.SalesOrderHeader;  
GO  
USE AdventureWorks2022;  
GO  
SELECT
    DATEDIFF(
            day,
            '2007-05-07 09:53:01.0376635',
            DATEADD(day, 1, SYSDATETIME())
        ) AS NumberOfDays  
    FROM Sales.SalesOrderHeader;  
GO

G. Especificando funções de classificação para startdate

Este exemplo usa uma função de classificação como um argumento para startdate.

USE AdventureWorks2022;  
GO  
SELECT p.FirstName, p.LastName  
    ,DATEDIFF(day, ROW_NUMBER() OVER (ORDER BY   
        a.PostalCode), SYSDATETIME()) AS 'Row Number'  
FROM Sales.SalesPerson s   
    INNER JOIN Person.Person p   
        ON s.BusinessEntityID = p.BusinessEntityID  
    INNER JOIN Person.Address a   
        ON a.AddressID = p.BusinessEntityID  
WHERE TerritoryID IS NOT NULL   
    AND SalesYTD <> 0;

H. Especificando uma função de janela de agregação para startdate

Este exemplo usa uma função de janela de agregação como um argumento para startdate.

USE AdventureWorks2022;  
GO  
SELECT soh.SalesOrderID, sod.ProductID, sod.OrderQty, soh.OrderDate,
    DATEDIFF(day, MIN(soh.OrderDate)   
        OVER(PARTITION BY soh.SalesOrderID), SYSDATETIME()) AS 'Total'  
FROM Sales.SalesOrderDetail sod  
    INNER JOIN Sales.SalesOrderHeader soh  
        ON sod.SalesOrderID = soh.SalesOrderID  
WHERE soh.SalesOrderID IN(43659, 58918);  
GO

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

-- DOES NOT ACCOUNT FOR LEAP YEARS
DECLARE @date1 DATETIME, @date2 DATETIME, @result VARCHAR(100);
DECLARE @years INT, @months INT, @days INT,
    @hours INT, @minutes INT, @seconds INT, @milliseconds INT;

SET @date1 = '1900-01-01 00:00:00.000'
SET @date2 = '2018-12-12 07:08:01.123'

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

Este é o conjunto de resultados.

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

Exemplos: Azure Synapse Analytics e PDW (Analytics Platform System)

Esses exemplos usam diferentes tipos de expressões como argumentos para os parâmetros startdate e enddate.

J. Especificando colunas para startdate e enddate

Este exemplo 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 TOP(1) DATEDIFF(day, startDate, endDate) AS Duration  
    FROM dbo.Duration;  
-- Returns: 1

K. Especificando subconsultas e funções escalares para startdate e enddate

Este exemplo usa subconsultas e funções escalares como argumentos para startdate e enddate.

-- Uses AdventureWorks  
  
SELECT TOP(1) DATEDIFF(day, (SELECT MIN(HireDate) FROM dbo.DimEmployee),  
    (SELECT MAX(HireDate) FROM dbo.DimEmployee))   
FROM dbo.DimEmployee;

L. Especificando constantes para startdate e enddate

Este exemplo usa constantes de caractere como argumentos para startdate e enddate.

-- Uses AdventureWorks  
  
SELECT TOP(1) DATEDIFF(day,
    '2007-05-07 09:53:01.0376635',
    '2007-05-08 09:53:01.0376635') FROM DimCustomer;

M. Especificando funções de classificação para startdate

Este exemplo usa uma função de classificação como um argumento para startdate.

-- Uses AdventureWorks  
  
SELECT FirstName, LastName,
    DATEDIFF(day, ROW_NUMBER() OVER (ORDER BY   
        DepartmentName), SYSDATETIME()) AS RowNumber  
FROM dbo.DimEmployee;

N. Especificando uma função de janela de agregação para startdate

Este exemplo usa uma função de janela de agregação como um argumento para startdate.

-- Uses AdventureWorks  
  
SELECT FirstName, LastName, DepartmentName,
    DATEDIFF(year, MAX(HireDate)  
        OVER (PARTITION BY DepartmentName), SYSDATETIME()) AS SomeValue  
FROM dbo.DimEmployee

Confira também

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

DATEDIFF (Transact-SQL)

Sintaxe

Argumentos

Tipo de retorno

Valor retornado

Limites de datepart

Comentários

Exemplos

a. Especificando colunas para startdate e enddate

B. Especificando variáveis definidas pelo usuário para startdate e enddate

C. Especificando funções de sistema escalares para startdate e enddate

D. Especificando subconsultas e funções escalares para startdate e enddate

E. Especificando constantes para startdate e enddate

F. Especificando expressões numéricas e funções de sistema escalares para enddate

G. Especificando funções de classificação para startdate

H. Especificando uma função de janela de agregação para startdate

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

Exemplos: Azure Synapse Analytics e PDW (Analytics Platform System)

J. Especificando colunas para startdate e enddate

K. Especificando subconsultas e funções escalares para startdate e enddate

L. Especificando constantes para startdate e enddate

M. Especificando funções de classificação para startdate

N. Especificando uma função de janela de agregação para startdate

Confira também

Comentários

Comentários

Recursos adicionais