Populate rows with all datetime values in a period?

Question

I'm looking to populate a column in a table with a value for every hour between two dates.

Example:

01-01-2020 00:00:00 to 01-01-2026 00:00:00

should produce this result:

01-01-2020 00:00:00
01-01-2020 01:00:00
01-01-2020 02:00:00
...
31-12-2025 22:00:00
31-12-2025 23:00:00
31-12-2025 22:00:00
01-01-2026 00:00:00

I know that I need DATEADD and DATEDIFF but I'm not figuring out the correct SQL statement for this

Paradisiacal

This is a bad idea; this means your row needs to have 365*7*24 columns; that's far too wide. – Melitamelitopol

Similar question as #27161974 – Via

What you should really do is invest in a calendar table and time table, then you can generate these as rows. – Melitamelitopol

When you say "populate row" do you really mean "populate column" ? Your desired results appear to be multiple rows – Severin

@MartinSmith - that's after someone else (not OP) edited the post. – Faint

@Faint - they just formatted it as code. The original post still had all the line breaks stackoverflow.com/revisions/… – Severin

@MartinSmith thats right it's column not row, that was a mistake – Paradisiacal

What version of SQL Server? A recent release added a feature that can make this easier. – Stewart

Answer 1

On SQL Server 2022 (or Azure SQL DB/MI), you can use GENERATE_SERIES:

DECLARE @start datetime = '20200101',
        @end   datetime = '20260101';

-- INSERT dbo.table(column)
SELECT h = DATEADD(HOUR, value, @start)
  FROM GENERATE_SERIES(0, DATEDIFF(HOUR, @start, @end)) AS x;

On SQL Server 2016 or better, you can use STRING_SPLIT(REPLICATE:

DECLARE @start datetime = '20200101',
        @end   datetime = '20260101';

-- INSERT dbo.table(column)
SELECT h = DATEADD(HOUR, ROW_NUMBER() OVER (ORDER BY @@SPID)-1, @start)
  FROM STRING_SPLIT
  (REPLICATE(CONVERT(varchar(max), ','), 
   DATEDIFF(HOUR, @start, @end)), ',') AS x;

On even older versions, well, you probably shouldn't be using anything older than that. (Never a bad idea to specify/tag with the specific version when asking your question.)

Here's a db<>fiddle but with a shorter date range because it doesn't gracefully handle thousands of output rows.

Generative

Learned something new today: I hadn't seen that REPLICATE/STRING_SPLIT hack before. (It took me a bit to figure out where the extra +1 was coming from. Answer: N commas splits into N+1 empty strings.) – Oncoming

@TN - For the case where you need <=8001 then a more concise variant is to use SPACE - but not applicable here as 365*7*24 easily exceeds that – Severin

Answer 2

If GENERATE_SERIES is not an option

Example

Declare @D1 datetime = '2020-01-01 00:00:00'
Declare @D2 datetime = '2026-01-01 00:00:00'

Select DTCol = dateadd(hour,N,@D1)
 From ( Select Top (Select datediff(hour,@D1,@D2)+1) N=-1+Row_Number() Over (Order By (Select NULL)) From master..spt_values n1, master..spt_values n2 ) A

Results

DTCol
2020-01-01 00:00:00.000
2020-01-01 01:00:00.000
2020-01-01 02:00:00.000
...
2025-12-31 22:00:00.000
2025-12-31 23:00:00.000
2026-01-01 00:00:00.000

Infrangible

The use of the under documented spt_values table is debateable #4274223 – Via

@BartMcEndree ANY table of adequate size will do. – Infrangible

Answer 3

hello thanks for the answers with the help of gpt this code made what i needed

DECLARE @StartDate DATETIME = '2020-01-01 00:00:00';
DECLARE @EndDate DATETIME = '2026-01-01 00:00:00';

DECLARE @currentDatetime DATETIME = @StartDate;

WHILE @currentDatetime < @EndDate
BEGIN

    DECLARE @valor INT = FLOOR(RAND() * 101);
    
    DECLARE @randomBit BIT = CASE WHEN RAND() < 0.5 THEN 0 ELSE 1 END;
    
    INSERT INTO teste (
        TimeStamp,
        Value_1,
        Value_2
        Value_3
        )
    VALUES (
        @currentDatetime,
        @valor,
        FLOOR(RAND() * 101),  
        @randomBit,          
     
    );
    
    SET @currentDatetime = DATEADD(HOUR, 1, @currentDatetime);
END

Paradisiacal

Thank you for contributing to the Stack Overflow community. This may be a correct answer, but it’d be really useful to provide additional explanation of your code so developers can understand your reasoning. This is especially useful for new developers who aren’t as familiar with the syntax or struggling to understand the concepts. Would you kindly edit your answer to include additional details for the benefit of the community? – Cordula