DATE_BUCKET (Transact-SQL)
Se aplica a:
SQL Server 2022 (16.x) Azure SQL DatabaseAzure SQL Managed Instance 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
Comentarios
Próximamente: A lo largo de 2024 iremos eliminando gradualmente GitHub Issues como mecanismo de comentarios sobre el contenido y lo sustituiremos por un nuevo sistema de comentarios. Para más información, vea: https://aka.ms/ContentUserFeedback.
Enviar y ver comentarios de