CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
Galma88
Galma88

Reputation: 2546

TSQL - Error in stored procedure due to conversion failure

I have this situation in a stored Procedure:

SET @DATE_RELEASE_START = '2015-01-01';
SET @DATE_RELEASE_END = '2015-05-31'

    SELECT @statement = ' SELECT * 
                          FROM (SELECT AFCDENTE, M.ID_MODIFICATION_CODE, COUNT(*) AS Conteggio--, CAST((COUNT(*)  * 100/ 15032) AS decimal(10,7)) AS Percentage
                                FROM CIC_LOG_MODIFICHE AS L INNER JOIN ADM_MODIFICATION_CODE AS M ON L.CD_MODIFICATION_CODE = M.CD_MODIFICATION_CODE
                                                            INNER JOIN CIC_PRODUZIONE AS P ON P.CD_CIC_PRODUZIONE = L.CD_CIC_PRODUZIONE
                                WHERE AFDTMODI BETWEEN '+@DATE_RELEASE_START+' AND '+@DATE_RELEASE_END+' AND P.CD_PLANT = '+@CD_PLANT+' AND AFCDENTE IS NOT NULL
                                GROUP BY AFCDENTE, M.ID_MODIFICATION_CODE) as tbl 
                        PIVOT (SUM(tbl.Conteggio) for tbl.ID_MODIFICATION_CODE in (' + @columns + ')) as pvt'

I get this error:

Conversion failed when converting date and/or time from character string.

I tried casting those dates but no changes. I get the same error.

What should I do?

Upvotes: 0

Views: 48

Answers (2)

Evaldas Buinauskas
Evaldas Buinauskas

Reputation: 14077

I'd suggest doing it like that:

SET @DATE_RELEASE_START = '2015-01-01';
SET @DATE_RELEASE_END = '2015-05-31'

    SELECT @statement = ' SELECT * 
                          FROM (SELECT AFCDENTE, M.ID_MODIFICATION_CODE, COUNT(*) AS Conteggio--, CAST((COUNT(*)  * 100/ 15032) AS decimal(10,7)) AS Percentage
                                FROM CIC_LOG_MODIFICHE AS L INNER JOIN ADM_MODIFICATION_CODE AS M ON L.CD_MODIFICATION_CODE = M.CD_MODIFICATION_CODE
                                                            INNER JOIN CIC_PRODUZIONE AS P ON P.CD_CIC_PRODUZIONE = L.CD_CIC_PRODUZIONE
                                WHERE AFDTMODI BETWEEN @p0 AND @p1 AND P.CD_PLANT = @p2 AND AFCDENTE IS NOT NULL
                                GROUP BY AFCDENTE, M.ID_MODIFICATION_CODE) as tbl 
                        PIVOT (SUM(tbl.Conteggio) for tbl.ID_MODIFICATION_CODE in (' + @columns + ')) as pvt'

EXECUTE sp_executesql @statement, N'@p0 DATETIME2, @p1 DATETIME2, @p0 NVARCHAR(1000)', @p0 = @DATE_RELEASE_START, @p1 = @DATE_RELEASE_END, @p2 = @CD_PLANT;

Instead casting them as VARCHAR, just pass them as variables and then use sp_executesql to do the right job.

Upvotes: 2

Thorsten Dittmar
Thorsten Dittmar

Reputation: 56697

You must quote the date strings in your SQL code properly:

WHERE AFDTMODI BETWEEN '''+@DATE_RELEASE_START+''' AND '''+@DATE_RELEASE_END+'''

Otherwise the SQL will read

WHERE AFDTMODI BETWEEN 2015-01-01 AND 2015-05-31

instead of

WHERE AFDTMODI BETWEEN '2015-01-01' AND '2015-05-31'

We've also had problems before with passing date/time values to stored procedures in Management Studio. We needed to give the date as 20150101 or 20150531.

Upvotes: 2

Related Questions