DATE_BUCKET (Transact-SQL)

Berlaku untuk: SQL Server 2022 (16.x) Azure SQL Database Azure SQL Managed Instance Azure SQL Edge

Fungsi ini mengembalikan nilai tanggal-waktu yang sesuai dengan awal setiap bucket tanggal-waktu dari tanda waktu yang ditentukan oleh origin parameter, atau nilai 1900-01-01 00:00:00.000 asal default jika parameter asal tidak ditentukan.

Lihat Jenis dan Fungsi Data Tanggal dan Waktu (Transact-SQL) untuk gambaran umum semua jenis dan fungsi data tanggal dan waktu Transact-SQL.

Konvensi sintaks transact-SQL

Sintaks

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

Argumen

datepart

Bagian tanggal yang digunakan dengan number parameter, misalnya, tahun, bulan, hari, menit, detik.

DATE_BUCKET tidak menerima variabel yang ditentukan pengguna yang setara untuk argumen datepart .

datepart	Singkatan
hari	dd, d
minggu	wk, ww
bulan	mm, m
kuartal	qq, q
tahun	yy, yyyy
jam	hh
menit	mi, n
detik	ss, s
milidetik	ms

number

Angka bilangan bulat yang memutuskan lebar wadah yang dikombinasikan dengan argumen datepart . Ini menunjukkan lebar wadah datepart dari waktu asal. Argumen ini tidak boleh berupa nilai bilangan bulat negatif.

date

Ekspresi yang bisa diatasi ke salah satu nilai berikut ini:

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

Untuk date, DATE_BUCKET akan menerima ekspresi kolom, ekspresi, atau variabel yang ditentukan pengguna jika mereka menyelesaikan ke salah satu jenis data yang disebutkan di atas.

asal

Ekspresi opsional yang bisa diatasi ke salah satu nilai berikut ini:

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

Tipe data untuk origin harus cocok dengan tipe data parameter date.

DATE_BUCKET menggunakan nilai 1900-01-01 00:00:00.000 tanggal asal default yaitu, pukul 12.00 pada hari Senin, 1 Januari 1900, jika tidak ada origin nilai yang ditentukan untuk fungsi tersebut.

Tipe hasil

Jenis data nilai yang dikembalikan untuk metode ini bersifat dinamis. Jenis pengembalian tergantung pada argumen yang disediakan untuk date. Jika jenis data input yang valid disediakan untuk date, maka DATE_BUCKET mengembalikan jenis data yang sama. DATE_BUCKET menimbulkan kesalahan jika string literal ditentukan untuk parameter date.

Mengembalikan nilai

Memahami keluaran dari DATE_BUCKET

DATE_BUCKET mengembalikan nilai tanggal atau waktu terbaru, yang sesuai dengan parameter datepart dan angka . Misalnya, dalam ekspresi di bawah ini, DATE_BUCKET akan mengembalikan nilai output 2020-04-13 00:00:00.0000000, karena output dihitung berdasarkan bucket satu minggu dari waktu asal default 1900-01-01 00:00:00.000. Nilainya 2020-04-13 00:00:00.0000000 adalah 6.276 minggu dari nilai 1900-01-01 00:00:00.000asal .

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

Untuk semua ekspresi di bawah ini, nilai output 2020-04-13 00:00:00.0000000 yang sama akan dikembalikan. Ini karena 2020-04-13 00:00:00.0000000 6.276 minggu dari tanggal asal, dan 6.276 dapat dibagi dengan 2, 3, 4 dan 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);

Output untuk ekspresi di bawah ini adalah 2020-04-06 00:00:00.0000000, yaitu 6275 minggu dari waktu asal default 1900-01-01 00:00:00.000.

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

Output untuk ekspresi di bawah ini adalah 2020-06-09 00:00:00.0000000, yaitu 75 minggu dari waktu asal yang ditentukan 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);

argumen datepart

dayofyear, day, dan weekday mengembalikan nilai yang sama. Setiap datepart dan singkatannya mengembalikan nilai yang sama.

argumen angka

Argumen angka tidak boleh melebihi rentang nilai int positif. Dalam pernyataan berikut, argumen untuk angka melebihi rentang int dengan 1. Pernyataan berikut mengembalikan pesan kesalahan, 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);

Jika nilai negatif untuk angka diteruskan ke fungsi DATE_BUCKET, kesalahan berikut akan dikembalikan.

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

argumen tanggal

DATE_BUCKET mengembalikan nilai dasar yang terkait dengan jenis data argumen date. Dalam contoh berikut, nilai output dengan jenis data datetime2 dikembalikan.

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

argumen asal

Jenis data dari origin dan argumen date dalamnya harus sama. Jika jenis data yang berbeda digunakan, kesalahan akan dihasilkan.

Keterangan

Gunakan DATE_BUCKET dalam klausa berikut:

• GROUP BY
• MEMILIKI
• ORDER BY
• DAFTAR SELECT <>
• WHERE

Contoh

J. Menghitung DATE_BUCKET dengan lebar wadah 1 dari waktu asal

Masing-masing pernyataan ini berlipat-lipat DATE_BUCKET dengan lebar wadah 1 dari waktu asal:

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

Berikut set hasilnya.

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. Menggunakan ekspresi sebagai argumen untuk parameter angka dan tanggal

Contoh ini menggunakan berbagai jenis ekspresi sebagai argumen untuk parameter angka dan tanggal.

Contoh-contoh ini dibangun menggunakan AdventureWorksDW2022 database.

Tentukan variabel yang ditentukan pengguna sebagai angka dan tanggal

Contoh ini menentukan variabel yang ditentukan pengguna sebagai argumen untuk angka dan tanggal:

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

Berikut set hasilnya.

1999-12-08 00:00:00.0000000

Tentukan kolom sebagai tanggal

Dalam contoh di bawah ini, kami menghitung jumlah OrderQuantity dan jumlah UnitPrice yang dikelompokkan di atas wadah tanggal mingguan.

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;

Berikut set hasilnya.

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

Tentukan fungsi sistem skalar sebagai tanggal

Contoh ini menentukan SYSDATETIME untuk tanggal. Nilai pasti yang dikembalikan tergantung pada hari dan waktu pelaksanaan pernyataan:

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

Berikut set hasilnya.

2020-03-02 00:00:00.0000000

Tentukan subkueri skalar dan fungsi skalar sebagai angka dan tanggal

Contoh ini menggunakan subkueri skalar, MAX(OrderDate), sebagai argumen untuk angka dan tanggal. (SELECT top 1 CustomerKey FROM dbo.DimCustomer where GeographyKey > 100) berfungsi sebagai argumen buatan untuk parameter angka, untuk memperlihatkan cara memilih argumen angka dari daftar nilai.

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

Tentukan ekspresi numerik dan fungsi sistem skalar sebagai angka dan tanggal

Contoh ini menggunakan ekspresi numerik ((10/2)), dan fungsi sistem skalar (SYSDATETIME) sebagai argumen untuk angka dan tanggal.

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

Tentukan fungsi jendela agregat sebagai angka

Contoh ini menggunakan fungsi jendela agregat sebagai argumen untuk angka.

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. Menggunakan nilai asal non-default

Contoh ini menggunakan nilai asal non default untuk membuat wadah tanggal.

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

Lihat juga

• CAST dan CONVERT (Transact-SQL)
• Jenis tanggal dan waktu
• Jenis dan fungsi data tanggal dan waktu (Transact-SQL)