Converting DateTime error when executing SQL stored procedure

I have written following stored procedure:

GO
/****** Object:  StoredProcedure [dbo].[ReadCounters]    Script Date: 08/17/2011 13:43:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- ReadCounters --
ALTER PROCEDURE [dbo].[ReadCounters]
    @type bit,
    @startDate DateTime,
    @endDate DateTime
AS
BEGIN
    DECLARE
    @AllCounterIds NVARCHAR(MAX),
    @Query NVARCHAR(MAX);

    SELECT @AllCounterIds = STUFF((
            SELECT DISTINCT '],[' + CAST([CounterId] AS VARCHAR(32))
            FROM AllCounters
            WHERE [DateTime] > @startDate AND [DateTime] < @endDate AND [Type] = @type
            for xml path('')), 1, 2, '') + ']';

    SET @Query = 'SELECT [DateTime], pvt.* from
                    (SELECT [Type], [DateTime], [Value], [CounterId]
                     FROM AllCounters WHERE CounterId IN 
                        (
                         SELECT DISTINCT([CounterId]) 
                         FROM AllCounters
                         WHERE [DateTime] > '''+ @startDate +''' AND [DateTime] < '''+ @endDate +''' AND [Type] = '+ @type +'
                        ) AND [DateTime] > '''+ @startDate +''' AND [DateTime] < '''+ @endDate +''' AND [Type] = '+ @type +'
                    ) S
                PIVOT
                (
                    SUM (Value)
                    FOR CounterId IN
                    (' + @AllCounterIds + ')
                ) AS pvt;';         
    EXECUTE(@Query);
END

Now when I try to execute this SP using any of the following way:

exec ReadCounters 1,'2013-10-05', '2011-11-30'
exec ReadCounters 1,'2013-10-05 00:00:00', '2011-11-30 00:00:00'
exec ReadCounters 1,'2013-10-05 00:00:00.000', '2011-11-30 00:00:00.000'开发者_如何转开发
exec ReadCounters 1,{ts '2013-10-05 00:00:00.000'}, {ts '2011-11-30 00:00:00.000'}

i get following error:

Msg 241, Level 16, State 1, Procedure ReadCounters, Line 19
Conversion failed when converting date and/or time from character string.

any suggestion why is giving me error. And if only execute the Select query it is running perfectly fine.

You need CONVERT of course to format it

....
WHERE [DateTime] > '''+ CONVERT(varchar(30), @startDate, 120) +''' AND ...
...

Why should SQL server guess that you want to concatenate different datatypes?

The error is because NVARCHAR(MAX) is lower priority then datetime as per these rules

I would rather do this - saves a lot of the messy conversions and red/black breaks (though I'm still going to recommend concatenation for the comma-separated list of IDs):

SET @Query = N'SELECT [DateTime], pvt.* from
            (SELECT [Type], [DateTime], [Value], [CounterId]
             FROM AllCounters WHERE CounterId IN 
                (
                 SELECT DISTINCT([CounterId]) 
                 FROM AllCounters
                 WHERE [DateTime] > @startDate AND [DateTime] < @endDate AND [Type] = @type 
                ) AND [DateTime] > @startDate AND [DateTime] < @endDate AND [Type] = @type 
            ) S
            PIVOT
            (
                SUM (Value)
                FOR CounterId IN
                (' + @AllCounterIds + ')
            ) AS pvt;';         

EXEC sp_executesql @query, 
    N'@startDate DATETIME, @endDate DATETIME, @type BIT',
    @startDate, @endDate, @type;