Dynamic Pivot String throwing MSG 241 Level 156 State 1 error: Conversion failed when converting Date and/or time from character string

Question

Dynamic Pivot String throwing MSG 241 Level 156 State 1 error: Conversion failed when converting Date and/or time from character string

EagleFire26 41

Hi Everyone I keep getting ,

MSG 241 Level 156 State 1 error: Conversion failed when converting Date and/or time
from character string when trying to print out my Dynamic SQL Query String to see
if it will print out correctly. I was curious I had quotes wrong or if my variables
for startdate and enddate which are datetime is incorrect and causing issues etc?

SET @SQLQUERY =
'
select * from
{
SELECT DISTINCT
QUOTENAME(CONCAT(YEAR(SHP.RECEIPT_DATE),''-'',FORMAT(MONTH(SHP.RECEIPT_DATE),''00''))) RECEIPT_MONTH
,QUOTENAME(CONCAT(YEAR(SHP.STATEMENT_START_DATE),''-'',FORMAT(MONTH(SHP.STATEMENT_START_DATE),''00''))) INCURRED_MONTH
,SHP.RBO_INST_ENCOUNTERS INST_ENCOUNTERS
FROM #TEMPSHP SHP
LEFT OUTER JOIN #TEMPHHR HHR ON SHP.CLAIM_HCC_ID = CLAIM_CLAIM_HCC_ID
WHERE RECEIPT_DATE BETWEEN ' + @STARTDATE + ' AND ' + @ENDDATE + ') AS BaseData
PIVOT
(SUM(INST_ENCOUNTERS)
FOR INCURRED_MONTH
IN (' + @PIVOTCOLUMN +') AS PIVOTTABLE

'

PRINT @SQLQUERY

0 comments

Answer accepted by question author

EchoLiu-MSFT 14,626

Hi @EagleFire26 ,

I don’t know what your data and your query look like, so I can’t advise you. Please provide a minimal example that we can test. This includes the code you are trying now and the CREATE and INSERT statements, as well as the expected output of your example.

Try:

 SET @SQLQUERY =  
 '  
 select * from  
 {  
 SELECT DISTINCT  
 QUOTENAME(CONCAT(YEAR(SHP.RECEIPT_DATE),''-'',FORMAT(MONTH(SHP.RECEIPT_DATE),''00''))) RECEIPT_MONTH  
 ,QUOTENAME(CONCAT(YEAR(SHP.STATEMENT_START_DATE),''-'',FORMAT(MONTH(SHP.STATEMENT_START_DATE),''00''))) INCURRED_MONTH  
 ,SHP.RBO_INST_ENCOUNTERS INST_ENCOUNTERS  
 FROM #TEMPSHP SHP  
 LEFT OUTER JOIN #TEMPHHR HHR ON SHP.CLAIM_HCC_ID = CLAIM_CLAIM_HCC_ID  
 WHERE RECEIPT_DATE BETWEEN ''' + cast(@STARTDATE as varchar) + ''' AND ''' +cast( @ENDDATEas varchar) + ''') AS BaseData  
 PIVOT  
 (SUM(INST_ENCOUNTERS)  
 FOR INCURRED_MONTH  
 IN (' + @PIVOTCOLUMN +') AS PIVOTTABLE  
      
 '  
      
 PRINT @SQLQUERY

Regards
Echo

If the answer is helpful, please click "Accept Answer" and upvote it.

0 comments

2 additional answers

Your answer

Answer 1

EchoLiu-MSFT 14,626

Or try:

SET @SQLQUERY =  
'  
select * from  
{  
SELECT DISTINCT  
QUOTENAME(CONCAT(YEAR(SHP.RECEIPT_DATE),''-'',FORMAT(MONTH(SHP.RECEIPT_DATE),''00''))) RECEIPT_MONTH  
,QUOTENAME(CONCAT(YEAR(SHP.STATEMENT_START_DATE),''-'',FORMAT(MONTH(SHP.STATEMENT_START_DATE),''00''))) INCURRED_MONTH  
,SHP.RBO_INST_ENCOUNTERS INST_ENCOUNTERS  
FROM #TEMPSHP SHP  
LEFT OUTER JOIN #TEMPHHR HHR ON SHP.CLAIM_HCC_ID = CLAIM_CLAIM_HCC_ID  
WHERE RECEIPT_DATE BETWEEN ' + cast(@STARTDATE as date) + ' AND ' +cast( @ENDDATEas date) + ') AS BaseData  
PIVOT  
(SUM(INST_ENCOUNTERS)  
FOR INCURRED_MONTH  
IN (' + @PIVOTCOLUMN +') AS PIVOTTABLE  
  
'  
  
PRINT @SQLQUERY

If you have any question, please feel free to let me know.

Regards
Echo

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

EagleFire26 41 Reputation points

2021-05-27T03:25:07.017+00:00

fThanks wish I was in a position that had a more powerful client than just Excel.
the sp_executesql does work, however it only returns 1 month (or 1 row of data) when it should be returning 36 month or 36 rows of data.

the alternative method throws a varchar and date date incompatible error in operand.

EchoLiu-MSFT 14,626

The following code returns the result I expected:

DECLARE @date DATE
SET @date=getdate()

DECLARE @sql varchar(max)
SET @sql=' select * from borrow where Ttime< '''+cast(@date as varchar)+''''
EXEC(@sql)

Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2021-05-27T06:29:06.26+00:00

If you are in Excel, why don't you do the pivoting in Excel instead? It has, in difference to SQL Server, built-in support for the task.
Naomi Nosonovsky 8,906 Reputation points

2021-05-27T16:45:22.293+00:00

Are you sure you have your dates set up correctly? Can you verify first that you get the correct result set using your start and end dates and without PIVOT first?

The approach I suggested is what you should be using - forget about trying to embed parameters into the dynamic SQL - you should not be doing that unless there is a very good reason for doing so (and I cannot think of any good reason for that).

BTW, I give you this advice and I always try to follow this in my own code, but quite often I am dealing with already working code using the wrong approach and even though I'd love to change it, I try to restrain myself (unless I really need to make changes in the code and in this case I would change that as well).
EagleFire26 41 Reputation points

2021-05-27T19:30:45.957+00:00

because the data set result I am pivoting with an aggregate is 6 million rows
EagleFire26 41 Reputation points

2021-05-27T19:31:20.857+00:00

Thanks the cast(@STARTDATE as date) + ' AND ' +cast( @ENDDATEas date solved the issue with the single row as well
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2021-05-27T21:39:25.693+00:00

because the data set result I am pivoting with an aggregate is 6 million rows

That's still six million rows after pivoting. Well, the number of rows are fewer, but the total number of cells is about the same.

Or do you mean that the raw data that you are aggregating is six million rows? Of course you should perform the aggregation in SQL Server before you return the data. That would be a straight GROUP BY query, and then you do apply the presentational device in the presentation layer, i.e. Excel.

Answer 2

Naomi Nosonovsky 8,906

Change your query to be:

    SET @SQLQUERY =
    '
    select * from
    {
    SELECT DISTINCT
    QUOTENAME(CONCAT(YEAR(SHP.RECEIPT_DATE),''-'',FORMAT(MONTH(SHP.RECEIPT_DATE),''00''))) RECEIPT_MONTH
    ,QUOTENAME(CONCAT(YEAR(SHP.STATEMENT_START_DATE),''-'',FORMAT(MONTH(SHP.STATEMENT_START_DATE),''00''))) INCURRED_MONTH
    ,SHP.RBO_INST_ENCOUNTERS INST_ENCOUNTERS
    FROM #TEMPSHP SHP
    LEFT OUTER JOIN #TEMPHHR HHR ON SHP.CLAIM_HCC_ID = CLAIM_CLAIM_HCC_ID
    WHERE RECEIPT_DATE BETWEEN @STARTDATE AND @ENDDATE ) AS BaseData
    PIVOT
    (SUM(INST_ENCOUNTERS)
    FOR INCURRED_MONTH
    IN (' + @PIVOTCOLUMN +') AS PIVOTTABLE ';

    PRINT @SQLQUERY;

    EXECUTE sp_ExecuteSQL @SQLQuery, N'@StartDate DATE, @EndDate DATE', @StartDate = @StartDate, @EndDate = @EndDate;

Don't embed the parameters inside the query - it is a very bad practice which will lead to errors like you're getting.

EagleFire26 41 Reputation points

2021-05-26T19:57:39.567+00:00

I have tried WHERE RECEIPT_DATE BETWEEN @STARTDATE AND @ENDDATE without the quotes but then the StartDate and EndDate variables wont come into sqlQuery script when its printed out
EagleFire26 41 Reputation points

2021-05-26T20:22:10.343+00:00

I also don't seem to have access to the sp_ExecuteSQL procedure in our system
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2021-05-26T22:03:16.193+00:00

The name is sp_executesql in all lowercase, and that matters on a case-sensitive system.

The way Naomi showed you is the way to go. The way you did it originally is difficult to get right.

Bear in mind that dynamic SQL is an advanced feature and not apt for programmers with low experience. Dynamic pivot is often better done client side.

Share via

Dynamic Pivot String throwing MSG 241 Level 156 State 1 error: Conversion failed when converting Date and/or time from character string

2 additional answers

Your answer