T-SQL - get date by dynamic params

I tried to figure out a dynamic query to get date col within past 20 days. The idea is quite simple and, moreover, I know the table does contain dates from getdate() to -20 days but still no result get returned

DECLARE @date_past_period varchar(MAX);
DECLARE @date_past_number varchar(M开发者_如何学GoAX);
SET @date_past_period='day';
SET @date_past_number='20';

DECLARE @aDate datetime;

DECLARE @sql varchar(MAX);
SET @sql='SELECT date FROM table WHERE convert(varchar,date,121) BETWEEN convert(varchar,getdate(),121) AND convert(varchar,dateadd('+@date_past_period+', -'+@date_past_number+', getdate()),121)';


exec(@sql);

Maybe the problem is in dynamic thing but I am not sure.

Any useful comment is appreciated

---

You can use CASE function (T-SQL):

CREATE PROCEDURE MyStoredProcedure 
@IntervalType VARCHAR(15),
@Num INT
AS

DECLARE @StartDate DATETIME = GETDATE();
DECLARE @EndDate DATETIME = 
    CASE @IntervalType
        WHEN 'DAY' THEN DATEADD(DAY,@Num,@StartDate)
        WHEN 'MONTH' THEN DATEADD(MONTH,@Num,@StartDate)
        WHEN 'YEAR' THEN DATEADD(YEAR,@Num,@StartDate)
    END;

IF @EndDate IS NULL
    RAISERROR('Invalid params', 16, 1);
ELSE
    SELECT date FROM table WHERE date BETWEEN @StartDate AND @EndDate;

By converting to VARCHAR your search condition from WHERE will not be SARG ( 1 & 2 ).

---

I am pretty sure this scenario can be covered without using dynamic SQL, however, one obvious problem in your SQL is the between clause - the range is in the wrong order. Try changing your @sql as below:

SET @sql='SELECT date FROM table WHERE convert(varchar,date,121) BETWEEN convert(varchar,dateadd('+@date_past_period+', -'+@date_past_number+', getdate()),121) AND convert(varchar,getdate(),121)';