DATE_BUCKET (Transact-SQL)

Artigo
10/16/2024

Importante

O SQL do Azure no Edge será desativado em 30 de setembro de 2025. Para obter mais informações e opções de migração, veja o Aviso de aposentadoria.

Observação

O SQL do Azure no Edge encerrou o suporte à plataforma ARM64.

Essa função retorna o valor datetime correspondente ao início de cada bucket datetime, do carimbo de data/hora definido pelo origin parâmetro ou do valor de origem padrão de 1900-01-01 00:00:00.000 if the origin parameter for not specified.

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 Transact-SQL

Sintaxe

DATE_BUCKET (datePart , number , date , origin)

Argumentos

datePart

A parte da data que é usada com o parâmetro 'number', conforme mostrado na tabela a seguir. DATE_BUCKET não aceita equivalentes de variáveis definidas pelo usuário para os argumentos datePart .

datePart	Abreviações
day	dd, d
week	wk, ww
month	mm, m
quarter	qq, q
year	yy, yyyy
hour	hh
minute	mi, n
second	ss, s
millisecond	ms

number

O número inteiro que decide a largura do bucket combinado com o argumento datePart . Isso representa a largura dos buckets datePart da hora de origem. Esse argumento deve ser um valor inteiro positivo .

date

Uma expressão que pode resolver um dos seguintes valores:

date
datetime
datetime2
datetimeoffset
smalldatetime
time

Para data, DATE_BUCKET aceita uma expressão de coluna, expressão ou variável definida pelo usuário se eles resolverem para qualquer um dos tipos de dados mencionados anteriormente.

origin

Uma expressão opcional que pode resolver um dos seguintes valores:

date
datetime
datetime2
datetimeoffset
smalldatetime
time

O tipo de dados para origem deve corresponder ao tipo de dados do parâmetro date .

DATE_BUCKET usa um valor de data de origem padrão de 1900-01-01 00:00:00.000, ou seja, 12:00 AM na segunda-feira, 1º de janeiro de 1900, se nenhum valor de origem for especificado para a função.

Tipo de retorno

O tipo de dados do valor retornado para esse método é dinâmico. O tipo de retorno depende do argumento fornecido para date. Se um tipo de dados de entrada válido for fornecido para data, DATE_BUCKET retornará o mesmo tipo de dados. DATE_BUCKET Gera um erro se um literal de cadeia de caracteres for especificado para o parâmetro DATE .

Valores retornados

Entenda a saída de `DATE_BUCKET`

DATE_BUCKET retorna o valor de data ou hora mais recente, correspondente aos parâmetros datePart e number . Por exemplo, nas expressões a seguir, DATE_BUCKET retorna o valor de saída de 2020-04-13 00:00:00.0000000, pois a saída é calculada com base em buckets de uma semana a partir do tempo de origem padrão de 1900-01-01 00:00:00.000. O valor de 2020-04-13 00:00:00.0000000 é 6276 semanas do valor de origem de 1900-01-01 00:00:00.000.

DECLARE @date DATETIME2 = '2020-04-15 21:22:11';

SELECT DATE_BUCKET(WEEK, 1, @date);

Para todas as expressões a seguir, o mesmo valor de saída de 2020-04-13 00:00:00.0000000 é retornado. Isso ocorre porque 2020-04-13 00:00:00.0000000 é 6276 semanas da data de origem e 6276 é divisível por 2, 3, 4 e 6.

DECLARE @date DATETIME2 = '2020-04-15 21:22:11';

SELECT DATE_BUCKET(WEEK, 2, @date);
SELECT DATE_BUCKET(WEEK, 3, @date);
SELECT DATE_BUCKET(WEEK, 4, @date);
SELECT DATE_BUCKET(WEEK, 6, @date);

A saída da expressão abaixo é 2020-04-06 00:00:00.0000000, que é de 6275 semanas a partir da hora de origem padrão 1900-01-01 00:00:00.000.

DECLARE @date DATETIME2 = '2020-04-15 21:22:11';

SELECT DATE_BUCKET(WEEK, 5, @date);

A saída da expressão abaixo é 2020-06-09 00:00:00.0000000, que é de 75 semanas a partir da hora de origem especificada 2019-01-01 00:00:00.

DECLARE @date DATETIME2 = '2020-06-15 21:22:11';
DECLARE @origin DATETIME2 = '2019-01-01 00:00:00';

SELECT DATE_BUCKET(WEEK, 5, @date, @origin);

Comentários

Use DATE_BUCKET nas seguintes cláusulas:

GROUP BY
HAVING
ORDER BY
SELECT <list>
WHERE

Argumento `datePart`

dayofyear, day e weekday retornam o mesmo valor. Cada datePart e suas abreviações retornam o mesmo valor.

argumento numérico

O argumento number não pode exceder o intervalo de valores int positivos. Nas instruções a seguir, o argumento number excede o intervalo de int em 1. A instrução a seguir retorna a seguinte mensagem de erro: Msg 8115, Level 16, State 2, Line 2. Arithmetic overflow error converting expression to data type int.

DECLARE @date DATETIME2 = '2020-04-30 00:00:00';

SELECT DATE_BUCKET(DAY, 2147483648, @date);

Se um valor negativo para number for passado para a DATE_BUCKET função, o seguinte erro será retornado.

Msg 9834, Level 16, State 1, Line 1
Invalid bucket width value passed to DATE_BUCKET function. Only positive values are allowed.

argumento de data

DATE_BUCKET Retorna o valor base correspondente ao tipo de dados do argumento de data . No exemplo a seguir, um valor de saída com o tipo de dados datetime2 é retornado.

SELECT DATE_BUCKET(DAY, 10, SYSUTCDATETIME());

argumento de origem

O tipo de dados dos argumentos de origem e data deve ser o mesmo. Se diferentes tipos de dados forem usados, um erro será gerado.

Exemplos

a. Calcular DATE_BUCKET com uma largura de bucket igual a 1 a partir da hora de origem

Cada uma dessas instruções é incrementada DATE_BUCKET com uma largura de bucket de 1 a partir da hora de origem:

DECLARE @date DATETIME2 = '2020-04-30 21:21:21';

SELECT 'Week', DATE_BUCKET(WEEK, 1, @date)
UNION ALL SELECT 'Day', DATE_BUCKET(DAY, 1, @date)
UNION ALL SELECT 'Hour', DATE_BUCKET(HOUR, 1, @date)
UNION ALL SELECT 'Minutes', DATE_BUCKET(MINUTE, 1, @date)
UNION ALL SELECT 'Seconds', DATE_BUCKET(SECOND, 1, @date);

Veja a seguir o conjunto de resultados.

Week    2020-04-27 00:00:00.0000000
Day     2020-04-30 00:00:00.0000000
Hour    2020-04-30 21:00:00.0000000
Minutes 2020-04-30 21:21:00.0000000
Seconds 2020-04-30 21:21:21.0000000

B. Usar expressões como argumentos para os parâmetros number e date

Estes exemplos usam diferentes tipos de expressões como argumentos para os parâmetros number e date. Esses exemplos são criados usando o AdventureWorksDW2019 banco de dados.

Especificar variáveis definidas pelo usuário como número e data

Este exemplo especifica variáveis definidas pelo usuário como argumentos para número e data:

DECLARE @days INT = 365,
    @datetime DATETIME2 = '2000-01-01 01:01:01.1110000';/* 2000 was a leap year */;

SELECT DATE_BUCKET(DAY, @days, @datetime);

Veja a seguir o conjunto de resultados.

---------------------------
1999-12-08 00:00:00.0000000

(1 row affected)

Especificar uma coluna como data

No exemplo a seguir, estamos calculando a soma de OrderQuantity e a soma de UnitPrice agrupados em buckets de datas semanais.

SELECT DATE_BUCKET(WEEK, 1, CAST(Shipdate AS DATETIME2)) AS ShippedDateBucket,
    Sum(OrderQuantity) AS SumOrderQuantity,
    Sum(UnitPrice) AS SumUnitPrice
FROM dbo.FactInternetSales FIS
WHERE Shipdate BETWEEN '2011-01-03 00:00:00.000'
        AND '2011-02-28 00:00:00.000'
GROUP BY DATE_BUCKET(week, 1, CAST(Shipdate AS DATETIME2))
ORDER BY ShippedDateBucket;

Veja a seguir o conjunto de resultados.

ShippedDateBucket           SumOrderQuantity SumUnitPrice
--------------------------- ---------------- ---------------------
2011-01-03 00:00:00.0000000 21               65589.7546
2011-01-10 00:00:00.0000000 27               89938.5464
2011-01-17 00:00:00.0000000 31               104404.9064
2011-01-24 00:00:00.0000000 36               118525.6846
2011-01-31 00:00:00.0000000 39               123555.431
2011-02-07 00:00:00.0000000 35               109342.351
2011-02-14 00:00:00.0000000 32               107804.8964
2011-02-21 00:00:00.0000000 37               119456.3428
2011-02-28 00:00:00.0000000 9                28968.6982

Especificar a função do sistema escalar como data

Este exemplo especifica SYSDATETIME para data. O valor exato retornado depende do dia e da hora da execução da instrução:

SELECT DATE_BUCKET(WEEK, 10, SYSDATETIME());

Veja a seguir o conjunto de resultados.

---------------------------
2020-03-02 00:00:00.0000000

(1 row affected)

Especificar subconsultas e funções escalares como número e data

Este exemplo usa subconsultas escalares, MAX(OrderDate), como argumentos para número e data. (SELECT top 1 CustomerKey FROM dbo.DimCustomer where GeographyKey > 100) funciona como um argumento artificial do parâmetro de número que mostra como selecionar um argumento number em uma lista de valores.

SELECT DATE_BUCKET(WEEK,
        (
            SELECT TOP 1 CustomerKey
            FROM dbo.DimCustomer
            WHERE GeographyKey > 100
        ),
        (
            SELECT MAX(OrderDate)
            FROM dbo.FactInternetSales
        )
    );

Especificar expressões numéricas e funções do sistema escalares como número e data

Este exemplo usa uma expressão numérica ((10/2)) e funções escalares do sistema (SYSDATETIME) como argumentos para número e data.

SELECT DATE_BUCKET(WEEK, (10 / 2), SYSDATETIME());

Especificar uma função de janela de agregação como número

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

SELECT DISTINCT DATE_BUCKET(DAY, 30, CAST([shipdate] AS DATETIME2)) AS DateBucket,
    FIRST_VALUE([SalesOrderNumber]) OVER (
        ORDER BY DATE_BUCKET(DAY, 30, CAST([shipdate] AS DATETIME2))
        ) AS FIRST_VALUE_In_Bucket,
    LAST_VALUE([SalesOrderNumber]) OVER (
        ORDER BY DATE_BUCKET(DAY, 30, CAST([shipdate] AS DATETIME2))
        ) AS LAST_VALUE_In_Bucket
FROM [dbo].[FactInternetSales]
WHERE ShipDate BETWEEN '2011-01-03 00:00:00.000'
        AND '2011-02-28 00:00:00.000'
ORDER BY DateBucket;
GO

C. Usar um valor de origem não padrão

Este exemplo usa um valor de origem não padrão para gerar os buckets de data.

DECLARE @date DATETIME2 = '2020-06-15 21:22:11';
DECLARE @origin DATETIME2 = '2019-01-01 00:00:00';

SELECT DATE_BUCKET(HOUR, 2, @date, @origin);

CAST e CONVERT (Transact-SQL)

Compartilhar via

DATE_BUCKET (Transact-SQL)

Sintaxe

Argumentos

datePart

number

date

origin

Tipo de retorno

Valores retornados

Entenda a saída de `DATE_BUCKET`

Comentários

Argumento `datePart`

argumento numérico

argumento de data

argumento de origem

Exemplos

a. Calcular DATE_BUCKET com uma largura de bucket igual a 1 a partir da hora de origem

B. Usar expressões como argumentos para os parâmetros number e date

Especificar variáveis definidas pelo usuário como número e data

Especificar uma coluna como data

Especificar a função do sistema escalar como data

Especificar subconsultas e funções escalares como número e data

Especificar expressões numéricas e funções do sistema escalares como número e data

Especificar uma função de janela de agregação como número

C. Usar um valor de origem não padrão

Comentários

Recursos adicionais

Compartilhar via

DATE_BUCKET (Transact-SQL)

Sintaxe

Argumentos

datePart

number

date

origin

Tipo de retorno

Valores retornados

Entenda a saída de DATE_BUCKET

Comentários

Argumento datePart

argumento numérico

argumento de data

argumento de origem

Exemplos

a. Calcular DATE_BUCKET com uma largura de bucket igual a 1 a partir da hora de origem

B. Usar expressões como argumentos para os parâmetros number e date

Especificar variáveis definidas pelo usuário como número e data

Especificar uma coluna como data

Especificar a função do sistema escalar como data

Especificar subconsultas e funções escalares como número e data

Especificar expressões numéricas e funções do sistema escalares como número e data

Especificar uma função de janela de agregação como número

C. Usar um valor de origem não padrão

Conteúdo relacionado

Comentários

Recursos adicionais

Entenda a saída de `DATE_BUCKET`

Argumento `datePart`