Get date range of a week, a month or a year that contains a date.

fnGetDateRange.sql

CREATE FUNCTION [dbo].[fnGetDateRange] 
(
    @Now DATETIME,
    @FrequencyId INT
)
RETURNS @Result TABLE 
(
    StartDate DATE, 
    EndDate DATE
)
AS
-- Test: SELECT * FROM dbo.fnGetDateRange('2021-2-20', 1)
BEGIN
    DECLARE @StartDate DATE
    DECLARE @EndDate DATE

    IF @FrequencyId = 1
    BEGIN
        -- Weekly
        SET @StartDate = DATEADD(DAY, 1 - DATEPART(WEEKDAY, @Now), @Now) -- Sunday
        SET @EndDate = DATEADD(DAY, 6, @StartDate) -- Saturday
    END

    IF @FrequencyId = 2
    BEGIN
        -- Monthly
        SET @StartDate = DATEADD(DAY, 1 - DAY(@Now), @Now)
        SET @EndDate = DATEADD(DAY, -1, DATEADD(MONTH, 1, @StartDate))
    END

    IF @FrequencyId = 3
    BEGIN
        -- Yearly
        DECLARE @Year VARCHAR(4)
        SET @Year = CAST(Year(@Now) AS VARCHAR(4))
        SET @StartDate = CONCAT(@Year, '-', '01', '-', '01')
        SET @EndDate = CONCAT(@Year, '-', '12', '-', '31')
    END

    INSERT INTO @Result
    VALUES
        (@StartDate, @EndDate)

    RETURN 
END
GO