'Insert date and time in SQL

i want to generate and insert date with time to table but dont quite know how. I need to insert date for every day to three years ahead with time

01.01.2023 07:00 01.01.2023 08:00 01.01.2023 09:00 02.01.2023 07:00 02.01.2023 08:00 02.01.2023 09:00 03.01.2023 07:00 and so on

sqldatetimeinsert


Solution 1:[1]

Recursive approach:

DECLARE 
    @StartDate DATE,
    @EndDate DATE

SELECT 
    @StartDate = '2023-01-01', 
    @EndDate = DATEADD(YEAR, 3, @StartDate);

WITH DatesCTE(RecDate) AS (
    SELECT @StartDate
    UNION ALL
    SELECT DATEADD(DAY, 1, RecDate) FROM DatesCTE WHERE RecDate < @EndDate
)
SELECT CAST(RecDate AS VARCHAR(10)) + ' 07:00' AS dt FROM DatesCTE
UNION ALL
SELECT CAST(RecDate AS VARCHAR(10)) + ' 08:00' AS dt FROM DatesCTE
UNION ALL
SELECT CAST(RecDate AS VARCHAR(10)) + ' 09:00' AS dt FROM DatesCTE
ORDER BY dt ASC
OPTION (MAXRECURSION 0)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 L0ndl3m

Related Questions