Datetime in Dynamic SQL

I’m trying to use datetime variables in a dynamic sql query. I’ve tried many queries but it never returns the right date. For example:

declare @StartDate datetime = '2020-01-10'
    SET @sql ='
        select dateadd(day, 1, '  +@StartDate  +')'
            execute sp_executesql @sql;

throws an error when converting date and/or time from character string. So I tried

declare @StartDate datetime = '2020-01-10'    
SET @sql ='
    select dateadd(day, 1, '  +convert(varchar(10),@StartDate, 101)  +')'
        execute sp_executesql @sql;

and that returns ‘1900-01-02’ which is obviously not the day after ‘2020-01-10’. So it keeps thinking my date is 0, which is converted to ‘1900-01-01’. What am I doing wrong here?

Answer

Pass in the date as a parameter!

set @sql ='
select dateadd(day, 1, @StartDate)';

execute sp_executesql @sql,  N'@StartDate date', @StartDate=@StartDate;

Use are using sp_executesql. Now use the parameters that it supports as well!

Advertisement

Answer