Date_Bucket (Transact-SQL)

Deze functie retourneert de datum/tijd-waarde die overeenkomt met het begin van elke datum/tijd-bucket, van de tijdstempel die is gedefinieerd door de parameter of de standaardwaarde van de oorsprong als de oorsprongparameter niet origin 1900-01-01 00:00:00.000 is opgegeven.

Zie Datum- en tijdgegevenstypen en -functies (Transact-SQL) voor een overzicht van alle Transact-SQL datum- en tijdgegevenstypen en -functies.

Syntaxisconvent SQL Transact-SQL

Syntaxis

DATE_BUCKET (datePart, number, date, origin)

Argumenten

Datepart

Het gedeelte van de datum dat wordt gebruikt met de parameter 'number'. Bijvoorbeeld Jaar, maand, minuut, seconde, enzovoort.

getal

Het gehele getal dat de breedte van de bucket bepaalt in combinatie met het argument datePart. Dit vertegenwoordigt de breedte van de dataPart-buckets van de oorsprongtijd. This argument cannot be a negative integer value.

date

Een expressie die kan worden opgelost naar een van de volgende waarden:

• date
• datum/tijd
• datetime2
• datetimeoffset
• smalldatetime
• time

Voor datum DATE_BUCKET accepteert een kolomexpressie, expressie of door de gebruiker gedefinieerde variabele als deze worden opgelost naar een van de hierboven genoemde gegevenstypen.

Oorsprong

Een optionele expressie die kan worden opgelost naar een van de volgende waarden:

• date
• datum/tijd
• datetime2
• datetimeoffset
• smalldatetime
• time

Het gegevenstype voor Origin moet overeenkomen met het gegevenstype van de parameter Date .

DATE_BUCKET gebruikt een standaardwaarde voor de oorsprongsdatum van 12:00 uur op maandag 1 januari 1900, als er geen origin-waarde is opgegeven voor de 1900-01-01 00:00:00.000 functie.

Retourtype

Het gegevenstype van de retourwaarde voor deze methode is dynamisch. Het retourtype is afhankelijk van het argument dat is opgegeven voor date . Als een geldig invoergegevenstype wordt opgegeven voor date , DATE_BUCKET retourneert hetzelfde gegevenstype. DATE_BUCKET geeft een fout als een letterlijke tekenreeks is opgegeven voor de date parameter.

Retourwaarden

Inzicht in de uitvoer van DATE_BUCKET

Date_Bucket retourneert de meest recente datum- of tijdwaarde, die overeenkomt met de parameter datePart en number. In de onderstaande expressies retourneert bijvoorbeeld de uitvoerwaarde van , omdat de uitvoer wordt berekend op basis van buckets van één week van de standaardtijd van Date_Bucket 2020-04-13 00:00:00.0000000 de oorsprong van 1900-01-01 00:00:00.000 . De waarde 2020-04-13 00:00:00.0000000 is 6276 weken vanaf de oorspronkelijke waarde van 1900-01-01 00:00:00.000 .

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

Voor alle onderstaande expressies wordt dezelfde uitvoerwaarde 2020-04-13 00:00:00.0000000 geretourneerd. Dit komt doordat 6276 weken vanaf de begindatum ligt en 2020-04-13 00:00:00.0000000 6276 deelbaar is door 2, 3, 4 en 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)

De uitvoer voor de onderstaande expressie is 2020-04-06 00:00:00.0000000 , die 6275 weken na de standaardtijd van de oorsprong 1900-01-01 00:00:00.000 is.

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

De uitvoer voor de onderstaande expressie is 2020-06-09 00:00:00.0000000 , die 75 weken na de opgegeven oorsprongtijd 2019-01-01 00:00:00 is.

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)

Argument datepart

dayofyear, day en weekday retourneren dezelfde waarde. Elk datumdeel en de afkortingen retourneren dezelfde waarde.

getalargument

Het argument number mag het bereik van positieve int-waarden niet overschrijden. In de volgende instructies overschrijdt het argument voor number het bereik van int met 1. De volgende instructie retourneert het volgende foutbericht: "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)

Als een negatieve waarde voor number wordt doorgegeven aan de Date_Bucket functie, wordt de volgende fout geretourneerd.

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 retourneert de basiswaarde die overeenkomt met het gegevenstype van het date argument . In het volgende voorbeeld wordt een uitvoerwaarde met datetime2-gegevenstype geretourneerd.

Select DATE_BUCKET(dd, 10, SYSUTCDATETIME())

origin Argument

Het gegevenstype van de origin argumenten en in moet hetzelfde date zijn. Als er verschillende gegevenstypen worden gebruikt, wordt er een fout gegenereerd.

Opmerkingen

Gebruik DATE_BUCKET in de volgende component:

• GROUP BY
• HAVING
• ORDER BY
• SELECTEER <list>
• WHERE

Voorbeelden

A. Een Date_Bucket met een bucketbreedte van 1 op basis van de oorspronkelijke tijd

Elk van deze instructies wordt date_bucket met een bucketbreedte van 1 vanaf de oorspronkelijke tijd:

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)

Hier is de resultatenset.

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. Expressies gebruiken als argumenten voor de parameters number en date

In deze voorbeelden worden verschillende typen expressies gebruikt als argumenten voor de getal- en datumparameters. Deze voorbeelden zijn gebouwd met behulp van de database AdventureWorksDW2017.

Door de gebruiker gedefinieerde variabelen opgeven als getal en datum

In dit voorbeeld worden door de gebruiker gedefinieerde variabelen opgegeven als argumenten voor getal en 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);

Dit is de resultatenset.

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

(1 row affected)

Een kolom opgeven als datum

In het onderstaande voorbeeld berekenen we de som van OrderQuantity en de som van UnitPrice gegroepeerd op wekelijkse 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

Dit is de resultatenset.

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

Scalaire systeemfunctie opgeven als datum

In dit voorbeeld wordt SYSDATETIME opgegeven voor datum. De exacte waarde die wordt geretourneerd, is afhankelijk van de dag en tijd van de uitvoering van de instructie:

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

Dit is de resultatenset.

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

(1 row affected)

Scalaire subquery's en scalaire functies opgeven als getal en datum

In dit voorbeeld worden scalaire subquery's, MAX(OrderDate) , gebruikt als argumenten voor getal en datum. (SELECT top 1 CustomerKey FROM dbo.DimCustomer where GeographyKey > 100)fungeert als een kunstmatig argument voor de getalparameter om te laten zien hoe u een getalargument selecteert in een waardelijst.

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

Numerieke expressies en scalaire systeemfuncties opgeven als getal en datum

In dit voorbeeld wordt een numerieke expressie ((10/2)) en scalaire systeemfuncties (SYSDATETIME) gebruikt als argumenten voor getal en datum.

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

Een statistische vensterfunctie opgeven als getal

In dit voorbeeld wordt een statistische vensterfunctie gebruikt als argument voor het getal.

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. Een niet-standaardwaarde voor oorsprong gebruiken

In dit voorbeeld wordt een niet-standaardwaarde voor oorsprong gebruikt om de datum-buckets te genereren.

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)

Zie ook

CAST en CONVERT (Transact-SQL)