Data type incompatibility in my Dynamic Pivot script

Question

so I've been working on a dynamic pivot script and I've almost got it to work, but I'm having issues with declared variables. Here's my code:

DECLARE @start_date DATE
DECLARE @end_date DATE

SET @start_date = CAST(DATEADD(dd,((DATEDIFF(dd,'17530101',GETDATE())/7)*7)-7,'17530101') AS DATE)
SET @end_date = CAST(DATEADD(dd,((DATEDIFF(dd,'17530101',GETDATE())/7)*7)-1,'17530101') AS DATE)

DECLARE @cols  AS NVARCHAR(MAX)='';
DECLARE @query AS NVARCHAR(MAX)='';

SELECT @cols = @cols + QUOTENAME(TicketDate) + ',' 
FROM 
    (SELECT DISTINCT TOP 14 ad.TicketDate 
    FROM AttendDet ad 
    WHERE CAST(ad.TicketDate AS DATE) BETWEEN  @start_date AND @end_date 
        AND ad.EmplCode IS NOT NULL 
    ORDER BY ad.TicketDate) as dates

SELECT @cols = SUBSTRING(@cols, 0, LEN(@cols)) 

SET @query =
'SELECT * FROM
(
    SELECT
        CAST(ad.EmplName AS NVARCHAR(MAX)) AS [EmplName],
        CAST(ad.TicketDate AS DATE) AS [TicketDate],
        ROUND(ad.TotActTime, 2) AS [TotalHrs]
    FROM AttendDet ad
    WHERE ad.EmplCode IS NOT NULL
        AND ad.AttendCode <> 9999
        AND CAST(ad.TicketDate AS DATE) BETWEEN (' + @start_date + ') AND (' + @end_date + ')
) basedata
PIVOT
(
    SUM(TotalHrs) FOR TicketDate IN (' + @cols + ')
) piv'

EXECUTE(@query)

The line that is causing an error is this one:

 AND CAST(ad.TicketDate AS DATE) BETWEEN (' + @start_date + ') AND (' + @end_date + ')

The error I get is: "The data types varchar and date are incompatible in the add operator." I tried changing the data types to VARCHAR, but that didn't work, when I do that I get this error: Conversion failed when converting date and/or time from character string.

I know I'm in the right direction, because if I hardcode that line manually, for example:

AND CAST(ad.TicketDate AS DATE) BETWEEN ''10/22/18'' AND ''10/28/18''

It works perfectly as expected, but I obviously don't want to do that, as it would defeat the entire purpose of the script. The whole thing should work as long as @start_data and @end_date are set, but I'm stuck on how to finish this. I've been trying all sorts of things for the last hour and can't figure it out

Thanks in advance

S3S · Accepted Answer · 2018-11-01T18:46:16.093

You need to cast it to varchar because SQL is interpreting the + as addition versus concatenation here.

DECLARE @start_date DATE = getdate()
DECLARE @end_date DATE = getdate()
DECLARE @cols varchar(10) = ''
DECLARE @query varchar(max)

SET @query =
'SELECT * FROM
(
    SELECT
        CAST(ad.EmplName AS NVARCHAR(MAX)) AS [EmplName],
        CAST(ad.TicketDate AS DATE) AS [TicketDate],
        ROUND(ad.TotActTime, 2) AS [TotalHrs]
    FROM AttendDet ad
    WHERE ad.EmplCode IS NOT NULL
        AND ad.AttendCode <> 9999
        AND CAST(ad.TicketDate AS DATE) BETWEEN ''' + cast(@start_date as varchar) + ''' AND ''' + cast(@end_date as varchar) + '''
) basedata
PIVOT
(
    SUM(TotalHrs) FOR TicketDate IN (' + @cols + ')
) piv'

print @query

So your query would be like so. Notice I changed the command to print instead of execute so you can see what it looks like:

DECLARE @start_date DATE
DECLARE @end_date DATE

SET @start_date = CAST(DATEADD(dd,((DATEDIFF(dd,'17530101',GETDATE())/7)*7)-7,'17530101') AS DATE)
SET @end_date = CAST(DATEADD(dd,((DATEDIFF(dd,'17530101',GETDATE())/7)*7)-1,'17530101') AS DATE)

DECLARE @cols  AS NVARCHAR(MAX)='';
DECLARE @query AS NVARCHAR(MAX)='';

SELECT @cols = @cols + QUOTENAME(TicketDate) + ',' 
FROM 
    (SELECT DISTINCT TOP 14 ad.TicketDate 
    FROM AttendDet ad 
    WHERE CAST(ad.TicketDate AS DATE) BETWEEN  @start_date AND @end_date 
        AND ad.EmplCode IS NOT NULL 
    ORDER BY ad.TicketDate) as dates

SELECT @cols = SUBSTRING(@cols, 0, LEN(@cols)) 

SET @query =
'SELECT * FROM
(
    SELECT
        CAST(ad.EmplName AS NVARCHAR(MAX)) AS [EmplName],
        CAST(ad.TicketDate AS DATE) AS [TicketDate],
        ROUND(ad.TotActTime, 2) AS [TotalHrs]
    FROM AttendDet ad
    WHERE ad.EmplCode IS NOT NULL
        AND ad.AttendCode <> 9999
        AND CAST(ad.TicketDate AS DATE) BETWEEN ''' + cast(@start_date as varchar) + ''' AND ''' + cast(@end_date as varchar) + '''
) basedata
PIVOT
(
    SUM(TotalHrs) FOR TicketDate IN (' + @cols + ')
) piv'

--EXECUTE(@query)
print @query

I'm getting "Operand type clash: date is incompatible with int". Not sure what that means. From running print, I do see that the IN looks correct, that's pretty neat, thanks — Cristian Contreras, Nov 01 '18 at 18:42
I forgot to add the extra tick marks. Try it now and it will work (bottom query). before i added the tick marks the sql would have been `between 2018-11-01 and ...` instead of `between '2018-11-01' and ...` which is correct — S3S, Nov 01 '18 at 18:46

score 0 · Answer 2 · answered Nov 01 '18 at 18:30

0

This should work:

    AND CAST(ad.TicketDate AS DATE) BETWEEN (' + CONVERT(varchar(11),@start_date,101) + ') AND (' + CONVERT(varchar(11),@end_date,101) + ')

answered Nov 01 '18 at 18:30

lije

420
2
15

score 0 · Answer 3 · answered Nov 01 '18 at 18:36

You have a couple of options.

Using the + operator to concatenate strings, all of the values have to be strings themselves, and you can't implicitly convert a DATE to a string, as your error messages says. You can, though, wrap both date variables in explicit CASTs, or CONVERTs if that's your bag:

(' + CAST(@start_date AS VARCHAR(10)) + ') AND (' + CAST(@end_date AS VARCHAR(10)) + ')
If your version supports it (2012+), just use CONCAT. It takes care of the conversions for you.

SET @query = CONCAT( 'SELECT * FROM ( SELECT CAST(ad.EmplName AS NVARCHAR(MAX)) AS [EmplName], CAST(ad.TicketDate AS DATE) AS [TicketDate], ROUND(ad.TotActTime, 2) AS [TotalHrs] FROM AttendDet ad WHERE ad.EmplCode IS NOT NULL AND ad.AttendCode <> 9999 AND CAST(ad.TicketDate AS DATE) BETWEEN (', @start_date, ') AND (', @end_date, ') ) basedata PIVOT ( SUM(TotalHrs) FOR TicketDate IN (', @cols, ') ) piv' )

Data type incompatibility in my Dynamic Pivot script

3 Answers3