CODEWITHSUNDEEP
sql-server-2008stored-proceduresdatetime-format
Safran Ali
Safran Ali

Reputation: 4497

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.

Upvotes: 2

Views: 5418

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

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;

Upvotes: 2

gbn
gbn

Reputation: 432180

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

Upvotes: 3

Related Questions