DATE_BUCKET performance versus conventional methods

A month and a half ago, I wrote a blog on using DATE_BUCKET. It is a cool feature thta makes doing some grouping really quite easy. It is here: Cool features in SQL Server I missed…DATE_BUCKET. One of the comments that came in was about performance of the DATE_BUCKET versus using things like DATEDIFF or a date table.

I started working on it then, but it got a bit involved (as performance comparison tests often do), so it took me a bit longer to get to than expected. But here it is, and the results are kind of what you would expect. The uses for DATE_BUCKET are really straightforward, and would rarely involve an index or a lot of filtering using the the function. But over a large number of rows, if it takes more time (even a millisecond more) than another method, you would notice it pretty quickly adding up.

The big question I want to answer is simply: “Is DATE_BUCKET more resource intensive than conventional methods using DATEPART or a Date Dimension, and is it so resource intensive that you need to avoid it altogether.

The Setup

In the Appendix is a script that will create a table with a million rows (and related data) to execute my queries on. This is a sufficient number of rows that should give a pretty decent comparison between methods. Running it on my pretty basic test machine, it only took around 2 minutes to complete.

The table that is created has this structure that represents some event that you may have logged.

			
CREATE TABLE EventLog
(
    EventId INT IDENTITY(1, 1) PRIMARY KEY,
    EventName NVARCHAR(255) NOT NULL,
    EventDescription NVARCHAR(MAX),
    EventTime DATETIME2 NOT NULL
        DEFAULT SYSUTCDATETIME(),
    Location NVARCHAR(255)
)

		

With this we will be able to run queries grouping data at different levels and comparing the plans and output of each.

Note, for each of these queries I will use a testing harness like this, so I can compare the plan and get the execution time and number of pages read with each method. I won’t show this in the blog after this, but I will show the output.

			
SET SHOWPLAN_TEXT ON;
GO
<query>
GO
SET SHOWPLAN_TEXT OFF;
GO
SET STATISTICS IO ON;
GO
SET STATISTICS TIME ON;
GO
<query>
GO
SET STATISTICS IO OFF;
GO
SET STATISTICS TIME OFF;
GO

		

To get the plan, the IO, and then the time for each query. As long as both queries use the same level of parallelism, I won’t worry about that because my goal is more to compare the generation of date values and see them grouped in the same manner. If there are differences in the plans, I will highlight them.

Working like `DATE_BUCKET`

The DATE_BUCKET function is kind of interesting, in that it basically just creates groupings of rounded off dates. Group by the year, and the output will resemble this:

			
YearBucket            
---------------------------
2025-01-01 00:00:00.0000000
2024-01-01 00:00:00.0000000

Grouping on a datetime2(7) output isn’t tremendously attractive, but it makes sense as you can use DATE_BUCKET to group even down to the .0000001 second (if you need that kind of grouping, I would LOVE to know what for!). So my first section will explore three solutions that output the data we are working with at the year, formatted like a datetime2(7) value. We will assume the UI will handle the groupings if this turns out to be the fastest method of them all.

Using `DATE_BUCKET`

Using DATE_BUCKET for this grouping is really simple. This is the query that we need.

			
SELECT DATE_BUCKET(YEAR, 1, EventTime) AS EventYearBucket,
       COUNT(*) AS NumberOfEvents
FROM dbo.EventLog
GROUP BY DATE_BUCKET(YEAR, 1, EventTime);

Execute this query and it will return something like :

			
EventYearBucket             NumberOfEvents 
--------------------------- -------------- 
2025-01-01 00:00:00.0000000 161733         
2024-01-01 00:00:00.0000000 161693         
2021-01-01 00:00:00.0000000 176415         
2023-01-01 00:00:00.0000000 161242         
2020-01-01 00:00:00.0000000 176785         
2022-01-01 00:00:00.0000000 162132    

		

Your output may vary, and I did not sort the data to cut down on details in the plan. The plan for this is:

The plan:

			
-------
  |--Parallelism(Gather Streams)
       |--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1006],0)))
            |--Hash Match(Aggregate, HASH:([Expr1002]), 
                RESIDUAL:([Expr1002] = [Expr1002]) DEFINE:([Expr1006]=COUNT(*)))
                 |--Compute Scalar(DEFINE:([Expr1002]=
                       Date_Bucket(year,(1),[tempdb].[dbo].[EventLog].[EventTime],NULL)))
                      |--Clustered Index Scan(OBJECT:(
                              [tempdb].[dbo].[EventLog].[PK__EventLog__7944C8105A063D89]))

		

And the important parts of the Statistics IO call:

			
Table 'EventLog'. Scan count 13, logical reads 37134
Table 'Worktable'. Scan count 0, logical reads 0
 SQL Server Execution Times:
   CPU time = 610 ms,  elapsed time = 90 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

		

So the plan indicates that this query did:

Scanned the entire table, which is to be expected since there is no filter at all
Computed a scalar value (the DATE_BUCKET value)
Used a Hash Match aggregate to count the number of rows in the table
Then computed the scalar expression for the COUNT(*)
Pulled the streams of data together, and output the rows.

Now, for comparison, I created the following version of this query, which outputs the same exact result:

Using `DATEADD`

We can output the a very same results using something like the following query.

			
SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, EventTime), 0) AS EventYearBucket,
       COUNT(*) AS NumberOfEvents
FROM dbo.EventLog
GROUP BY DATEADD(YEAR, DATEDIFF(YEAR, 0, EventTime), 0);

This outputs the same result set, and the plan is basically the same (with the one difference being the scalar being computed is not as simple:

  |--Parallelism(Gather Streams)
       |--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1006],0)))
            |--Hash Match(Aggregate, HASH:([Expr1002]), 
                     RESIDUAL:([Expr1002] = [Expr1002]) DEFINE:([Expr1006]=COUNT(*)))
                 |--Compute Scalar(DEFINE:([Expr1002]=dateadd(year,
                             CONVERT_IMPLICIT(bigint,datediff(year,'1900-01-01 00:00:00.000',
                                CONVERT_IMPLICIT(datetime,[tempdb].[dbo].[EventLog].[EventTime],0)),0),
                                                                           '1900-01-01 00:00:00.000')))
                      |--Clustered Index Scan(
                                    OBJECT:([tempdb].[dbo].[EventLog].[PK__EventLog__7944C8105A063D89]))

And the IO and CPU statistics:

			
Table 'EventLog'. Scan count 13, logical reads 37134
Table 'Worktable'. Scan count 0, logical reads 0
 SQL Server Execution Times:
   CPU time = 375 ms,  elapsed time = 48 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

		

Same basic plan, same basic IO, both using parallelism, but one thing IS different. The CPU time has increased by double (I tested this a few times on my machine in different orders, though none of this is very scientific.) Now, I did process 1 million rows, and the amount of time went from .04 seconds to .09 seconds basically, on a non-server class computer. But if you are paying for CPU time and doing something like this regularly, it coudl make a difference.

Using a date dimension

In the Appendix, I included a table with date information you can use to do the followings groupings. So I rewrote the query in a way that uses no date functions.

			
SELECT DateDimension.Year AS EventYearBucket,
       COUNT(*) AS NumberOfEvents
FROM dbo.EventLog
       JOIN DateDimension
          on cast(EventLog.EventTime as date) = DateDimension.Date
             and DateDimension.Hour = 0
GROUP BY DateDimension.year;

		

Predictably, the plan is different:

			
|--Parallelism(Gather Streams)
   |--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1008],0)))
        |--Hash Match(Aggregate, HASH:([tempdb].[dbo].[DateDimension].[Year]) 
                                                  DEFINE:([Expr1008]=COUNT(*)))
              |--Hash Match(Inner Join, HASH:([tempdb].[dbo].[DateDimension].[Date])
                            =([Expr1005]), RESIDUAL:([Expr1005]=[tempdb].[dbo].[DateDimension].[Date]))
                   |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[DateDimension].[PKDateDimension]),  
                                      WHERE:([tempdb].[dbo].[DateDimension].[Hour]=(0)))
                   |--Compute Scalar(DEFINE:([Expr1005]=CONVERT(date,[tempdb].[dbo].[EventLog].[EventTime],0)))
                        |--Clustered Index Scan(
                             OBJECT:([tempdb].[dbo].[EventLog].[PK__EventLog__7944C8105A063D89]))

		

Now we get:

Scan of the EventLog table
A scalar compute to get the EventTime as a date datatype (which could have been rolled into the table, but I wanted to keep it fair.
A Hatch Match join on the date dimension and the base table
The rest is the same..
Then computed the scalar expression for the COUNT(*)
Pulled the streams of data together, and output the rows.

Then the IO and CPU Stats

			
Table 'EventLog'. Scan count 13, logical reads 37134, ph
Table 'DateDimension'. Scan count 13, logical reads 944,
Table 'Worktable'. Scan count 0, logical reads 0, physic
 SQL Server Execution Times:
   CPU time = 358 ms,  elapsed time = 52 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

		

This was one of the better executions of this and you can see that the times are very similar, but you do have the extra scans and reads for the DateDimension table. Basically a wash between the DATEADD method, paying for the join or the scalar computations. Get rid of the cast to a date and this would be better.

Now to make more appealing output

Usually, the output that is wanted is something like the following. Grouped by Year, Quarter,Month or something similar. So in this example, I will be using the built-in functions to group by, and then format, the DATE_BUCKET output, then using the DATEDIFF and a Date Dimension to see how much it affects the costs when I am doing more than one DATE_BUCKET calculation.

Using `DATE_BUCKET`

Just like before, I will use DATE_BUCKET, but this time I need to repeat the DATE_BUCKET a few times to calculate data to output:

			
SELECT YEAR(DATE_BUCKET(YEAR, 1, EventTime)) AS EventYearBucket,
       DATEPART(Quarter,(DATE_BUCKET(QUARTER, 1, EventTime))) AS EventQuarterBucket,
       MONTH(DATE_BUCKET(MONTH, 1, EventTime)) AS EventMonthBucket,
       COUNT(*) AS NumberOfEvents
FROM dbo.EventLog
GROUP BY DATE_BUCKET(YEAR, 1, EventTime),
         DATE_BUCKET(QUARTER, 1, EventTime),
         DATE_BUCKET(MONTH, 1, EventTime)

		

This will return something like the following.

			
EventYearBucket EventQuarterBucket EventMonthBucket NumberOfEvents
--------------- ------------------ ---------------- --------------
          4                  11               13361
          1                  3                13540
          1                  1                13693
          4                  12               14830
          3                  7                14958
          2                  5                13707
          2                  6                14415

		

Just like before I am not including an ORDER BY clause to keep that out of the plan for simplicity sake. The plan this time is basically the same with a few more scalars:

			
|--Parallelism(Gather Streams)
   |--Compute Scalar(DEFINE:([Expr1006]=datepart(year,[Expr1002]), 
              [Expr1007]=datepart(quarter,[Expr1003]), [Expr1008]=datepart(month,[Expr1004])))
       |--Compute Scalar(DEFINE:([Expr1005]=CONVERT_IMPLICIT(int,[Expr1011],0)))
          |--Hash Match(Aggregate, HASH:([Expr1002], [Expr1003], [Expr1004]), 
               RESIDUAL:([Expr1002] = [Expr1002] AND [Expr1003] = [Expr1003] 
                    AND [Expr1004] = [Expr1004]) DEFINE:([Expr1011]=COUNT(*)))
                   |--Compute Scalar(DEFINE:([Expr1002]=Date_Bucket(
                              year,(1),[tempdb].[dbo].[EventLog].[EventTime],NULL), 
                              [Expr1003]=Date_Bucket(quarter,(1),[tempdb].[dbo].[EventLog].[EventTime],NULL), 
                              [Expr1004]=Date_Bucket(month,(1),[tempdb].[dbo].[EventLog].[EventTime],NULL)))
                        |--Clustered Index Scan(OBJECT:(
                                  [tempdb].[dbo].[EventLog].[PK__EventLog__7944C8105A063D89]))

		

Still it does the same, scan the table, compute the scalars, use a Hash Match algorithm to gather the groupings, compute the COUNT(*) and the gather the streams and output the rows. Since the scalars are different for the output, you can see that they are valuated a second time as well.

The output stats are similar to the previous executions as well:

			
Table 'EventLog'. Scan count 13, logical reads 37134
Table 'Worktable'. Scan count 0, logical reads 0, ph
 SQL Server Execution Times:
   CPU time = 1860 ms,  elapsed time = 177 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

		

Overall, it took about 3 times as much CPU, and twice as long as it do previously, whcih since we are doing the calculation 3 times, it makes sense..

Using DATEDIFF

This query is a lot more natural than the DATE_BUCKET version because we are just pulling out the factors to calculate from the date, without the need to do two operations to get them in an output format we desire.

			
SELECT YEAR(EventTime) AS EventYearBucket,
       DATEPART(Quarter,EventTime) AS EventQuarterBucket,
       MONTH(EventTime) AS EventMonthBucket,
       COUNT(*) AS NumberOfEvents
FROM dbo.EventLog
GROUP BY YEAR(EventTime),
         DATEPART(Quarter,EventTime),
         MONTH(EventTime); 

		

The plan is very similar to the plan from the DATE_BUCKET example, but you can see the compute scalars look less complicated:

			
|--Parallelism(Gather Streams)
    |--Compute Scalar(DEFINE:([Expr1005]=CONVERT_IMPLICIT(int,[Expr1008],0)))
         |--Hash Match(Aggregate, HASH:([Expr1002], [Expr1003], [Expr1004]), 
                RESIDUAL:([Expr1002] = [Expr1002] AND [Expr1003] = [Expr1003] 
                AND [Expr1004] = [Expr1004]) DEFINE:([Expr1008]=COUNT(*)))
             |--Compute Scalar(DEFINE:([Expr1002]=datepart(year,[tempdb].[dbo].[EventLog].[EventTime]), 
                 [Expr1003]=datepart(quarter,[tempdb].[dbo].[EventLog].[EventTime]), 
                 [Expr1004]=datepart(month,[tempdb].[dbo].[EventLog].[EventTime])))
                   |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[EventLog].[PK__EventLog__7944C8105A063D89]))

		

And the same IO:

			
Table 'EventLog'. Scan count 13, logical reads 37134
Table 'Worktable'. Scan count 0, logical reads 0, ph
 SQL Server Execution Times:
   CPU time = 456 ms,  elapsed time = 67 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

		

But the CPU time is considerably less (both do still do parallelism). This would generally make sense since our scalars we are computing is far less complicated (and we ended up using the same methods to get the formatted output.

Using a Date dimension

OF course, the standard way to do these things in a data warehouse is to use a date dimension. It saves having to do these calculations over and over, but it adds in the cost of the extra table and the join.

			
SELECT DateDimension.Year AS EventYearBucket,
       DateDimension.Quarter as EventQuarterBucket,
       DateDimension.Month as EventMonthBucket,
       COUNT(*) AS NumberOfEvents
FROM dbo.EventLog
       JOIN DateDimension
          on cast(EventLog.EventTime as date) = DateDimension.Date
             and DateDimension.Hour = 0
GROUP BY DateDimension.Year,
       DateDimension.Quarter,
       DateDimension.Month; 

		

The plan shows it to be a bit more complex in some ways:

			
|--Parallelism(Gather Streams)
   |--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1008],0)))
       |--Hash Match(Aggregate, HASH:([tempdb].[dbo].[DateDimension].[Year], 
                 [tempdb].[dbo].[DateDimension].[Quarter], [tempdb].[dbo].[DateDimension].[Month]), 
                  RESIDUAL:([tempdb].[dbo].[DateDimension].[Year] = [tempdb].[dbo].[DateDimension].[Year] 
                  AND [tempdb].[dbo].[DateDimension].[Quarter] = [tempdb].[dbo].[DateDimension].[Quarter] 
                  AND [tempdb].[dbo].[DateDimension].[Month] = [tempdb].[dbo].[DateDimension].[Month]) 
                  DEFINE:([Expr1008]=COUNT(*)))
          |--Hash Match(Inner Join, HASH:([tempdb].[dbo].[DateDimension].[Date])=([Expr1005]), 
                 RESIDUAL:([Expr1005]=[tempdb].[dbo].[DateDimension].[Date]))
                 |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[DateDimension].[PKDateDimension]),  
                    WHERE:([tempdb].[dbo].[DateDimension].[Hour]=(0)))
                 |--Compute Scalar(DEFINE:([Expr1005]=CONVERT(date,[tempdb].[dbo].[EventLog].[EventTime],0)))
                      |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[EventLog].[PK__EventLog__7944C8105A063D89]))

		

But it basically behaves like the previous example, except now in the Hash Match aggregate, there are three dimensions being output. You will also see the same IO stats as the single group by dimension

			
Table 'EventLog'. Scan count 13, logical reads 37134, ph
Table 'DateDimension'. Scan count 13, logical reads 944,
Table 'Worktable'. Scan count 0, logical reads 0, physic
 SQL Server Execution Times:
   CPU time = 344 ms,  elapsed time = 43 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

		

But overall, the time for this execution was consistency improved over previous examples.

Conclusion

DATE_BUCKET is just a bit slower in execution time than the other two example queries I tested it against. In some respects it’s clumsy output is hurting it as a function, because most users want to see their data groups named something cleaner than a datetime2(7) variable when grouping by a year.

However, this does not mean we should just kill the concept of DATE_BUCKET entirely. For doing standard time frames, I would consider avoiding it and using a date dimension or DATEPART and other date functions. But note, DATE_BUCKET is very very flexible. You can use it to group in days, weeks, hours, minutes etc very easily, and you can vary the start date of your functions easily. If you want to group the data in your table in two week segments starting on Tuesday, no problem. If you don’t do it regularly enough to create a date dimension that handles that time frame, good luck getting that set up using the date functions.

The performance differences seen on my low powered test machine show that DATE_BUCKET, while more resource intensive/slower than some conventional methods might be for conventional output; is not so resource intensive that I would avoid using it when it is indeed the best method. Knowing when each method is better is a matter for the problems you are attempting to solve. but for sure DATE_BUCKET deserves a place in your toolbox.

Appendix

Here are the main tables and data that will be used in the table. Run this script to get that data. I used SQL Prompt AI to create the table and data for the examples. This and all the code can be found on my GitHub repo here.

			
USE Tempdb;
SET NOCOUNT ON; --don't send a lot of messages about row affected
SET XACT_ABORT ON; --stop processing on error
IF OBJECT_ID(   'EventLog',
                'U'
            ) IS NOT NULL
    DROP TABLE dbo.EventLog;
CREATE TABLE EventLog
(
    EventId INT IDENTITY(1, 1) PRIMARY KEY,
    EventName NVARCHAR(255) NOT NULL,
    EventDescription NVARCHAR(MAX),
    EventTime DATETIME2 NOT NULL
        DEFAULT SYSUTCDATETIME(),
    Location NVARCHAR(255)
);
-- SQL script to generate a parameterized number of random 
-- events between 2020-01-01 and 2025-12-31 for the EventLog table. 
-- Default to 1000
--1,000,000 took an hour on my machine. <machine stats>
DECLARE @NumberOfEvents INT = 1000000; -- Parameter for number 
                                      --of events to generate
DECLARE @StartDate DATETIME2 = '2020-01-01';
DECLARE @EndDate DATETIME2 = '2025-12-31';
DECLARE @EventNames TABLE
(
    EventName NVARCHAR(255)
);
DECLARE @Locations TABLE
(
    Location NVARCHAR(255)
);
DECLARE @i INT = 1;
-- Populate sample event names
INSERT INTO @EventNames
(
    EventName
)
VALUES('System Start'),
      ('System Shutdown'),
      ('Login Attempt'),
      ('Login Success'),
      ('Login Failure'),
      ('Data Export'),
      ('Data Import'),
      ('Configuration Change'),
      ('Backup Completed'),
      ('Restore Operation'),
      ('Security Alert'),
      ('Database Maintenance'),
      ('User Created'),
      ('User Deleted'),
      ('Permission Change'),
      ('Server Error'),
      ('Application Error'),
      ('Memory Warning'),
      ('Disk Space Warning'),
      ('Performance Threshold Exceeded');
-- Populate sample locations
INSERT INTO @Locations
(
    Location
)
VALUES('Server Room A'),
      ('Server Room B'),
      ('Data Center 1'),
      ('Data Center 2'),
      ('Cloud Instance East'),
      ('Cloud Instance West'),
      ('Office Network'),
      ('Remote Location'),
      ('Disaster Recovery Site'),
      ('Development Environment'),
      ('Testing Environment'),
      ('Production Environment'),
      ('Branch Office'),
      ('Headquarters'),
      ('Mobile Device'),
      ('Client Location');
-- Generate random events
WHILE @i <= @NumberOfEvents
    BEGIN
        DECLARE @RandomEventName NVARCHAR(255);
        DECLARE @RandomLocation NVARCHAR(255);
        DECLARE @RandomDescription NVARCHAR(MAX);
        DECLARE @RandomDate DATETIME2;
        -- Select random event name
        SELECT   TOP(1)
                 @RandomEventName = EventName
        FROM     @EventNames
        ORDER BY NEWID();
        -- Select random location
        SELECT   TOP(1)
                 @RandomLocation = Location
        FROM     @Locations
        ORDER BY NEWID();
        -- Generate random date between start and end date
        SET @RandomDate
            = DATEADD(   SECOND,
                         ABS(CHECKSUM(NEWID())) % DATEDIFF(   SECOND,
                                                              @StartDate,
                                                              @EndDate
                                                          ),
                         @StartDate
                     );
        -- Generate random description
        SET @RandomDescription
            = 'Event ' + @RandomEventName + ' occurred at ' + @RandomLocation
              + ' on ' + CONVERT(   NVARCHAR,
                                    @RandomDate,
                                    120
                                ) + '. Reference ID: '
              + CONVERT(   NVARCHAR,
                           ABS(CHECKSUM(NEWID()))
                       );
        -- Insert event
        INSERT INTO dbo.EventLog
        (
            EventName,
            EventDescription,
            EventTime,
            Location
        )
        VALUES(@RandomEventName,
               @RandomDescription,
               @RandomDate,
               @RandomLocation);
        SET @i = @i + 1;
    END
-- Verify the number of rows inserted
SELECT COUNT(*) AS [Total Events Generated]
FROM   dbo.EventLog;
SELECT   TOP(10)
         *
FROM     dbo.EventLog
ORDER BY EventTime;
--create and load a date table that will group data by day, month, quarter, year, and hour
-- Create a Date dimension table
DROP TABLE IF EXISTS dbo.DateDimension;
CREATE TABLE dbo.DateDimension
(
    Date DATETIME2(0) NOT NULL,
    Hour INT NOT NULL,
    DateTimeValue datetime2(0),
    Day INT NOT NULL,
    DayOfWeek INT NOT NULL,
    DayName NVARCHAR(10) NOT NULL,
    DayOfMonth INT NOT NULL,
    DayOfYear INT NOT NULL,
    WeekOfYear INT NOT NULL,
    Month INT NOT NULL,
    MonthName NVARCHAR(10) NOT NULL,
    Quarter INT NOT NULL,
    QuarterName NVARCHAR(6) NOT NULL,
    Year INT NOT NULL,
    IsWeekend BIT NOT NULL
    CONSTRAINT PKDateDimension PRIMARY KEY (Date, Hour),
    YearStartTime as (date_bucket(year,1, DateTimeValue)) PERSISTED,
    QuarterStartTime as date_bucket(quarter,1, DateTimeValue)  PERSISTED,
    MonthStartTime as date_bucket(month,1, DateTimeValue)  PERSISTED
);
GO
SET NOCOUNT ON;
SET STATISTICS IO OFF;
set statistics time OFF;
-- Declare variables for date range - adjust as needed for your data
DECLARE @StartDate DATE = '2020-01-01';
DECLARE @EndDate DATE = '2027-01-01';
DECLARE @CurrentDate DATE = @StartDate;
-- Populate the date dimension table
WHILE @CurrentDate <= @EndDate
BEGIN
    -- Loop through each hour of the day
    DECLARE @Hour INT = 0;
    WHILE @Hour < 24
    BEGIN
        INSERT INTO dbo.DateDimension
        (
            Date,
            Hour,
            DateTimeValue,
            Day,
            DayOfWeek,
            DayName,
            DayOfMonth,
            DayOfYear,
            WeekOfYear,
            Month,
            MonthName,
            Quarter,
            QuarterName,
            Year,
            IsWeekend
        )
        SELECT @CurrentDate,
               @hour,
               DATEADD(hour,@hour,cast(@CurrentDate as datetime2(0))),
               DAY(@CurrentDate),
               DATEPART(WEEKDAY, @CurrentDate),
               DATENAME(WEEKDAY, @CurrentDate),
               DAY(@CurrentDate),
               DATEPART(DAYOFYEAR, @CurrentDate),
               DATEPART(WEEK, @CurrentDate),
               MONTH(@CurrentDate),
               DATENAME(MONTH, @CurrentDate),
               DATEPART(QUARTER, @CurrentDate),
               'Q' + CAST(DATEPART(QUARTER, @CurrentDate) AS VARCHAR(1)),
               YEAR(@CurrentDate),
               CASE
                   WHEN DATEPART(WEEKDAY, @CurrentDate) IN ( 1, 7 ) THEN
                       1
                   ELSE
                       0
               END;
        SET @Hour = @Hour + 1;
    END
    SET @CurrentDate = DATEADD(DAY, 1, @CurrentDate);
END;

		

Drsql's Database Musings