question

EagleFire26-2202 avatar image
0 Votes"
EagleFire26-2202 asked ErlandSommarskog commented

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

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






sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
1 Vote"
EchoLiu-msft answered EchoLiu-msft edited

Hi @EagleFire26-2202,

100132-image.png

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.

100093-image.png
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.




image.png (17.7 KiB)
image.png (12.1 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

NaomiNNN avatar image
0 Votes"
NaomiNNN answered ErlandSommarskog commented

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.



· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

0 Votes 0 ·

I also don't seem to have access to the sp_ExecuteSQL procedure in our system

0 Votes 0 ·

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.

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

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.



· 7
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

0 Votes 0 ·

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)
0 Votes 0 ·

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.

0 Votes 0 ·

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

0 Votes 0 ·
Show more comments

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).

0 Votes 0 ·

Thanks the cast(@STARTDATE as date) + ' AND ' +cast( @ENDDATEas date solved the issue with the single row as well

0 Votes 0 ·