DATEDIFF (Transact-SQL)

  • Artigo

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

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.

Consulte DATEDIFF_BIG para obter uma função que lida com diferenças maiores entre os valores startdate e enddate. Consulte Tipos de dados e funções de data e hora para obter uma visão geral de todos os tipos de dados e funções de data e hora do Transact-SQL.

Convenções de sintaxe de Transact-SQL

Sintaxe

DATEDIFF ( datepart , startdate , enddate )

Argumentos

datepart

Especifica as unidades nas quais DATEDIFF relata a diferença entre a data de início e a data de término. Unidades de datepart usadas com frequência incluem month ou second.

O valor 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 da parte da data ou qualquer abreviação listada do nome completo.

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
weekday dw, w
hour hh
minute mi, n
second ss, s
millisecond ms
microsecond mcs
nanosecond ns

Cada nome de datepart específico e abreviações para esse nome de datepart retornam o 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 Configuração do servidor: corte de ano de dois dígitos para obter informações sobre valores de ano de dois dígitos.

enddate

Consulte startdate.

Tipos 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, sugerindo que 2036 deve ser um ano bissexto. Este caso significa que se começarmos em startdate 2036-03-01, e depois contarmos -2 os dias, atingimos a enddate de 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 a data de início e a data de término é de 24 dias, 20 horas, 31 minutos e 23,647 segundos. Para second, a diferença máxima é de 68 anos, 19 dias, 3 horas, 14 minutos e 7 segundos.

Se startdate e enddate forem atribuídos apenas a um valor de hora e a datepart não for uma data 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 é preciso apenas para o minuto, segundos e milissegundos são sempre definidos como 0 no valor retornado quando startdate ou enddate têm um valor smalldatetime .

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 tipos de dados de data diferentes e um tiver mais partes de tempo 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 valores de ano diferentes, mas tiverem os mesmos valores de semana do calendário, DATEDIFF retornará 0 para datepart week.

Comentários

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

DATEDIFF converte implicitamente literais de cadeias de caracteres como um tipo datetime2. Em outras palavras, DATEDIFF não dá suporte ao formato YDM quando a data é passada como uma cadeia de caracteres. Você deve converter explicitamente a cadeia de caracteres em um tipo datetime ou smalldatetime para usar o YDM formato.

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 pode estourar com uma precisão de minute ou superior, se a diferença entre enddate e startdate retornar um valor que está fora do intervalo para int.

Exemplos

Os exemplos de código do Transact-SQL deste artigo usa o banco de dados de exemplo AdventureWorks2022 ou AdventureWorksDW2022, que pode ser baixado da home page Microsoft SQL Server Samples and Community Projects.

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

R. Especificar colunas para data de início e data de término

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;

Veja a seguir o conjunto de resultados.

Duration
--------
1

B. Especificar 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 AS DATETIME2 = '2007-05-05 12:10:09.3312722';
DECLARE @enddate AS DATETIME2 = '2007-05-04 12:10:09.3312722';

SELECT DATEDIFF(day, @startdate, @enddate);

C. Especificar funções escalares do sistema para startdate e enddate

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

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

D. Especificar subconsultas escalares 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. Especificar 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. Especificar expressões numéricas e funções do sistema escalar 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. Especificar 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 AS s
     INNER JOIN Person.Person AS p
         ON s.BusinessEntityID = p.BusinessEntityID
     INNER JOIN Person.Address AS a
         ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL
      AND SalesYTD <> 0;

H. Especificar uma função de janela agregada 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 AS sod
     INNER JOIN Sales.SalesOrderHeader AS soh
         ON sod.SalesOrderID = soh.SalesOrderID
WHERE soh.SalesOrderID IN (43659, 58918);
GO

I. Encontre a diferença entre startdate e enddate como strings de partes de data

-- DOES NOT ACCOUNT FOR LEAP YEARS
DECLARE @date1 AS DATETIME, @date2 AS DATETIME, @result AS VARCHAR (100);

DECLARE @years AS INT, @months AS INT, @days AS INT, @hours AS INT, @minutes AS INT, @seconds AS INT, @milliseconds AS 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;

Veja a seguir 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. Especificar colunas para data de início e data de término

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;

Duration
--------
1

K. Especificar subconsultas escalares 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. Especificar 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. Especificar 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. Especificar uma função de janela agregada 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;

Conteúdo relacionado