DateTime manipulation in SQL Server EXECUTE sp_executesql

Question

I am trying to do the following:

EXECUTE sp_executesql
    N'SELECT TOP 10 * FROM dbo.Items WHERE DateCreated BETWEEN @start AND @end'
    , N'@start DATETIME, @end DATETIME'
    , @start = '20091001'
    , @end = GETDATE() --problem is caused by this line

Error:
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ')'.

I need to manipulate the date and pass it in as parameter, i.e. Monday of the week, Month of the year etc. Is it even possible?

Thanks.

Answer 1

You need to declare a variable, I think it has trouble with using a function:

DECLARE @endDate as datetime
SET @endDate = GETDATE()
EXECUTE sp_executesql
    N'SELECT TOP 10 * FROM dbo.Items WHERE DateCreated BETWEEN @start AND @end'
    , N'@start DATETIME, @end DATETIME'
    , @start = '20091001'
    , @end = @endDate