DATE_BUCKET (Transact-SQL)

  • Artículo

Se aplica a: SQL Server 2022 (16.x) Azure SQL Database Azure SQL Instancia administrada Azure SQL Edge

Esta función devuelve el valor de fecha y hora correspondiente al inicio de cada cubo de fecha y hora, desde la marca de tiempo definida por el parámetro origin o el valor de origen predeterminado de 1900-01-01 00:00:00.000, si el parámetro de origen no se ha especificado.

Para obtener una introducción sobre todos los tipos de datos y funciones de fecha y hora de Transact-SQL, vea Tipos de datos y funciones de fecha y hora (Transact-SQL).

Convenciones de sintaxis de Transact-SQL

Sintaxis

DATE_BUCKET (datepart, number, date [, origin ] )

Argumentos

datepart

Parte del valor date que se usa con el parámetro number, por ejemplo, year, month, day, minute, second.

DATE_BUCKET no acepta los equivalentes de variables definidas por el usuario para los argumentos datepart.

datepart Abreviaturas
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

número

Número entero que decide el ancho del cubo combinado con el argumento datepart. Representa el ancho de los cubos de datepart desde la hora de origen. Este argumento no puede ser un valor entero negativo.

date

Una expresión que se puede resolver en uno de los valores siguientes:

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

Para data, DATE_BUCKET aceptará una expresión de columna, una expresión o una variable definida por el usuario si se resuelven en cualquiera de los tipos de datos mencionados anteriormente.

origin

Expresión opcional que se puede resolver en uno de los valores siguientes:

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

El tipo de datos de origin debe coincidir con el tipo de datos del parámetro date.

DATE_BUCKET usa un valor de fecha de origen predeterminado de 1900-01-01 00:00:00.000, es decir, 12:00 a. m. del lunes, 1 de enero de 1900, si no se especifica ningún valor origin para la función.

Tipo de valor devuelto

El tipo de datos de valores devueltos de este método es dinámico. El tipo devuelto depende del argumento proporcionado para date. Si se proporciona otro tipo válido de datos de entrada para date, DATE_BUCKET devuelve el mismo tipo de datos. DATE_BUCKET genera un error si se especifica un literal de cadena para el parámetro date.

Valores devueltos

Descripción del resultado de DATE_BUCKET

DATE_BUCKET devuelve el valor de fecha u hora más reciente, que corresponde a datepart y al parámetro number. Por ejemplo, en las expresiones siguientes, DATE_BUCKET devolverá el valor de salida de 2020-04-13 00:00:00.0000000, ya que la salida se calcula en función de los cubos de una semana a partir de la hora de origen predeterminada de 1900-01-01 00:00:00.000. El valor 2020-04-13 00:00:00.0000000 es 6276 semanas desde el valor de origen 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 las expresiones siguientes, se devolverá el mismo valor de salida de 2020-04-13 00:00:00.0000000. Esto se debe a que 2020-04-13 00:00:00.0000000 es 6276 semanas a partir de la fecha de origen y 6276 es divisible por 2, 3, 4 y 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);

La salida de la expresión siguiente es 2020-04-06 00:00:00.0000000, que es 6275 semanas a partir de la hora de origen predeterminada 1900-01-01 00:00:00.000.

DECLARE @date DATETIME2 = '2020-04-15 21:22:11';
SELECT DATE_BUCKET(WEEK, 5, @date);

La salida de la expresión siguiente es 2020-06-09 00:00:00.0000000, que es 75 semanas a partir de la hora de origen 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);

Argumento datepart

dayofyear, day y weekday devuelven el mismo valor. Cada datepart y sus abreviaturas devuelven el mismo valor.

Argumento number

El argumento number no puede superar el intervalo de valores int positivos. En estas instrucciones, el argumento para number supera el intervalo de int en uno. La siguiente instrucción devuelve el mensaje de error siguiente: 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);

Si se pasa un valor negativo para number a la función DATE_BUCKET, se devolverá el siguiente error.

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

Argumento date

DATE_BUCKET devuelve el valor base correspondiente al tipo de datos del argumento date. En el ejemplo siguiente, se devuelve un valor de salida con el tipo de datos datetime2.

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

Argumento origin

El tipo de datos de los argumentos origin y date debe ser el mismo. Si se usan tipos de datos diferentes, se generará un error.

Observaciones

Use DATE_BUCKET en las cláusulas siguientes:

  • GROUP BY
  • HAVING
  • ORDER BY
  • SELECT <lista>
  • WHERE

Ejemplos

A. Cálculo de DATE_BUCKET con un ancho de cubo de 1 a partir de la hora de origen

Cada una de estas instrucciones incrementa DATE_BUCKET con un ancho de cubo de 1 a partir de la hora de origen:

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);

Este es el 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. Uso de expresiones como argumentos para los parámetros "number" y "date"

En los ejemplos siguientes se usan otros tipos de expresiones como argumentos para los parámetros number y date.

Estos ejemplos se compilan con la base de datos AdventureWorksDW2022.

Especificación de variables definidas por el usuario como número y fecha

En este ejemplo se especifican variables definidas por el usuario como argumentos para number y date:

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

Este es el conjunto de resultados.

1999-12-08 00:00:00.0000000

Especificación de una columna como fecha

En el ejemplo siguiente, se calcula la suma de OrderQuantity y la suma de UnitPrice agrupadas en cubos de fechas semanales.

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;

Este es el 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

Especificación de la función de sistema escalar como fecha

En este ejemplo se especifica SYSDATETIME para date. El valor devuelto exacto depende del día y la hora de ejecución de la instrucción:

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

Este es el conjunto de resultados.

2020-03-02 00:00:00.0000000

Especificación de subconsultas y funciones escalares como número y fecha

En este ejemplo se usan subconsultas escalares, MAX(OrderDate), como argumentos para number y date. (SELECT top 1 CustomerKey FROM dbo.DimCustomer where GeographyKey > 100) actúa como argumento artificial para el parámetro "number", para mostrar cómo se selecciona un argumento number de una lista de valores.

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

Especificación de expresiones numéricas y funciones del sistema escalares como número y fecha

En este ejemplo se usa una expresión numérica ((10/2)) y funciones del sistema escalares (SYSDATETIME) como argumentos de número y fecha.

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

Especificación de una función de ventana agregada como número

En este ejemplo se usa una función de ventana agregada como 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. Uso de un valor de origen no predeterminado

En este ejemplo se usa un valor de origen no predeterminado para generar los cubos de fecha.

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);

Consulte también