Date_Bucket (Transact-SQL)

Den här funktionen returnerar datetime-värdet som motsvarar starten av varje datetime-bucket, från tidsstämpeln som definieras av parametern eller standardvärdet för ursprung om origin 1900-01-01 00:00:00.000 ursprungsparametern inte har angetts.

I Datatyper och funktioner för datum och tid (Transact-SQL) en översikt över alla datatyper och funktioner för Transact-SQL och tid.

Syntaxkonventioner SQL Transact-SQL

Syntax

DATE_BUCKET (datePart, number, date, origin)

Argument

Datumdel

Den del av datumet som används med parametern "number". t.ex. År, månad, minut, sekund osv.

Nummer

Heltalsnumret som avgör bredden på bucketen kombinerat med argumentet datePart. Detta representerar bredden på dataPart-bucketarna från ursprungstiden. This argument cannot be a negative integer value.

date

Ett uttryck som kan matchas med något av följande värden:

• date
• datetime
• datetime2
• datetimeoffset
• smalldatetime
• Tid

För datum accepterar ett kolumnuttryck, uttryck eller en användardefinierad variabel om de matchar någon av DATE_BUCKET de datatyper som nämns ovan.

Ursprung

Ett valfritt uttryck som kan matchas med något av följande värden:

• date
• datetime
• datetime2
• datetimeoffset
• smalldatetime
• Tid

Datatypen för Origin ska matcha parameterns Date datatyp.

DATE_BUCKET använder standardvärdet för ursprungsdatum, 1900-01-01 00:00:00.000 dvs. 12:00 måndagen den 1 januari 1900, om inget värde för Ursprung har angetts för funktionen.

Returtyp

Datatypen för returvärdet för den här metoden är dynamisk. Returtypen beror på det argument som anges för date . Om en giltig indatatyp anges för date DATE_BUCKET returnerar samma datatyp. DATE_BUCKET höjer ett fel om en stränglitteral har angetts för date parametern.

Returvärden

Förstå utdata från DATE_BUCKET

Date_Bucket returnerar det senaste datum- eller tidsvärdet, som motsvarar parametern datePart och number. I uttrycken nedan returneras till exempel utdatavärdet för , eftersom utdata beräknas baserat på en Date_Bucket 2020-04-13 00:00:00.0000000 veckas bucketar från standardtiden för ursprunget . 1900-01-01 00:00:00.000 Värdet är 2020-04-13 00:00:00.0000000 6276 veckor från ursprungsvärdet för 1900-01-01 00:00:00.000 .

declare @date datetime2 = '2020-04-15 21:22:11'
Select DATE_BUCKET(wk, 1, @date)

För alla uttryck nedan returneras samma 2020-04-13 00:00:00.0000000 utdatavärde för. Det beror på att är 6276 veckor från ursprungsdatum och 2020-04-13 00:00:00.0000000 6276 är delbart med 2, 3, 4 och 6.

declare @date datetime2 = '2020-04-15 21:22:11'
Select DATE_BUCKET(wk, 2, @date)
Select DATE_BUCKET(wk, 3, @date)
Select DATE_BUCKET(wk, 4, @date)
Select DATE_BUCKET(wk, 6, @date)

Utdata för uttrycket nedan är 2020-04-06 00:00:00.0000000 , vilket är 6275 veckor från standardtiden för 1900-01-01 00:00:00.000 ursprunget .

declare @date datetime2 = '2020-04-15 21:22:11'
Select DATE_BUCKET(wk, 5, @date)

Utdata för uttrycket nedan är 2020-06-09 00:00:00.0000000 , vilket är 75 veckor från den angivna ursprungstiden 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(wk, 5, @date, @origin)

datepart-argument

dayofyear, day och weekday returnerar samma värde. Varje datepart och dess förkortningar returnerar samma värde.

talargument

Argumentet number får inte överskrida intervallet för positiva int-värden. I följande instruktioner överskrider argumentet för tal intervallet för int med 1. Följande instruktion returnerar följande felmeddelande: "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(dd, 2147483648, @date)

Om ett negativt värde för tal skickas Date_Bucket till funktionen returneras följande fel.

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

datumargument

DATE_BUCKET returnera basvärdet som motsvarar datatypen för date argumentet. I följande exempel returneras ett utdatavärde med datetime2-datatyp.

Select DATE_BUCKET(dd, 10, SYSUTCDATETIME())

origin Argument

Datatypen för origin argumenten date och i måste vara samma. Om olika datatyper används genereras ett fel.

Kommentarer

Använd DATE_BUCKET i följande satser:

• GROUP BY
• HAVING
• ORDNA EFTER
• VÄLJ <list>
• WHERE

Exempel

A. Beräkna Date_Bucket med bucketbredden 1 från ursprungstiden

Var och en av dessa instruktioner date_bucket med bucketbredden 1 från ursprungstiden:

declare @date datetime2 = '2020-04-30 21:21:21'
Select 'Week',  DATE_BUCKET(wk, 1, @date)
Union All
Select 'Day',  DATE_BUCKET(dd, 1, @date)
Union All
Select 'Hour',  DATE_BUCKET(hh, 1, @date)
Union All
Select 'Minutes',  DATE_BUCKET(mi, 1, @date)
Union All
Select 'Seconds',  DATE_BUCKET(ss, 1, @date)

Här är resultatuppsättningen.

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. Använda uttryck som argument för tal- och datumparametrar

I de här exemplen används olika typer av uttryck som argument för tal- och datumparametrarna. De här exemplen skapas med databasen "AdventureWorksDW2017".

Ange användardefinierade variabler som tal och datum

I det här exemplet anges användardefinierade variabler som argument för tal och datum:

DECLARE @days int = 365,
        @datetime datetime2 = '2000-01-01 01:01:01.1110000'; /* 2000 was a leap year */;
SELECT Date_Bucket(day, @days, @datetime);

Här är resultatuppsättningen.

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

(1 row affected)

Ange en kolumn som datum

I exemplet nedan beräknar vi summan av OrderQuantity och summan av UnitPrice grupperade över veckovisa datum-buckets.

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 1

Här är resultatuppsättningen.

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

Ange skalärsystemfunktion som datum

Det här exemplet anger SYSDATETIME för datum. Det exakta värde som returneras beror på dag och tid för instruktionskörning:

SELECT Date_Bucket(wk, 10, SYSDATETIME());

Här är resultatuppsättningen.

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

(1 row affected)

Ange skalära underfrågor och skalärfunktioner som tal och datum

I det här exemplet används skalära underfrågor, MAX(OrderDate) , som argument för tal och datum. (SELECT top 1 CustomerKey FROM dbo.DimCustomer where GeographyKey > 100) fungerar som ett artificiellt argument för nummerparametern för att visa hur du väljer ett talargument från en värdelista.

SELECT DATE_BUCKET(week,(SELECT top 1 CustomerKey FROM dbo.DimCustomer where GeographyKey > 100),
    (SELECT MAX(OrderDate) FROM dbo.FactInternetSales));

Att ange numeriska uttryck och skalära systemfunktioner som tal och datum

I det här exemplet används ett numeriskt uttryck ((10/2)) och skalära systemfunktioner (SYSDATETIME) som argument för tal och datum.

SELECT Date_Bucket(week,(10/2), SYSDATETIME());

Ange en aggregeringsfönsterfunktion som tal

I det här exemplet används en aggregeringsfönsterfunktion som argument för talet.

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. Använda ett ursprung som inte är standardvärde

I det här exemplet används ett ursprung som inte är standardvärde för att generera datum buckets.

declare @date datetime2 = '2020-06-15 21:22:11'
declare @origin datetime2 = '2019-01-01 00:00:00'
Select DATE_BUCKET(hh, 2, @date, @origin)

Se även

CAST och CONVERT (Transact-SQL)