My guess is that the SQL statement returns more than one row. A scalar variable can contain only one value. Use a Table variable instead.
DECLARE @t1 TABLE([date] DATETIME);
INSERT INTO @t1 ([date]) SELECT DISTINCT plandate AS [Date]
FROM plannedgangs
WHERE (plandate BETWEEN @plandatefrom AND @plandateto);
SELECT [Date] FROM @t1;
Assuming that
plandate
in
plannedgangs
is a
DateTime
Data Type, you do not need the
CONVERT
function. If it is not a
DateTime
Data Type, then the
between
clause will not work correctly.