SQL query to generate a selection of date ranges based off parameters specified.

sp_GenerateDateRangeSelectQuery.sql

CREATE OR ALTER PROCEDURE sp_GenerateDateRangeSelectQuery
    @TimeInterval NVARCHAR(10),  -- 'HOUR', 'DAY', 'WEEK', 'MONTH'
    @StartDate DATETIME,
    @EndDate DATETIME,
    @SchemaName NVARCHAR(128),
    @TableName NVARCHAR(128),
    @DateColumnName NVARCHAR(128)
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX)
    DECLARE @CurrentStart DATETIME
    DECLARE @CurrentEnd DATETIME


    IF @TimeInterval NOT IN ('HOUR', 'DAY', 'WEEK', 'MONTH')
    BEGIN
        PRINT 'Invalid interval type specified. Use HOUR, DAY, WEEK, or MONTH.'
        RETURN
    END

    SET @CurrentStart = @StartDate
    SET @CurrentEnd = @StartDate

    -- Begin loop through the range
    WHILE @CurrentEnd < @EndDate
    BEGIN
        -- Calculate the next end date based on the interval type
        IF @TimeInterval = 'HOUR'
            SET @CurrentEnd = DATEADD(HOUR, 1, @CurrentStart)
        ELSE IF @TimeInterval = 'DAY'
            SET @CurrentEnd = DATEADD(DAY, 1, @CurrentStart)
        ELSE IF @TimeInterval = 'WEEK'
            SET @CurrentEnd = DATEADD(WEEK, 1, @CurrentStart)
        ELSE IF @TimeInterval = 'MONTH'
            SET @CurrentEnd = DATEADD(MONTH, 1, @CurrentStart)

        -- Adjust the end date to not exceed the overall end date
        IF @CurrentEnd > @EndDate
            SET @CurrentEnd = @EndDate

        /*SET @SQL = 'SELECT * FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + 
                   ' WHERE ' + QUOTENAME(@DateColumnName) + ' BETWEEN ''' + 
                   CONVERT(NVARCHAR, @CurrentStart, 120) + ''' AND ''' + 
                   CONVERT(NVARCHAR, @CurrentEnd, 120) + ''';'*/
                   
        SET @SQL = 'SELECT * FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + 
                   ' WHERE ' + QUOTENAME(@DateColumnName) + ' >= ''' + 
                   CONVERT(NVARCHAR, @CurrentStart, 120) + ''' AND ' + QUOTENAME(@DateColumnName) + ' < ''' + 
                   CONVERT(NVARCHAR, @CurrentEnd, 120) + ''';'

        PRINT @SQL
        
        SET @CurrentStart = @CurrentEnd
    END
END
GO