CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Jurij
Jurij

Reputation: 125

Why am I getting the error datetime2 clashing with int?

I have a sp that inserts data from one table to another; when I run this, I get an operand type clash for line 7 (insert tab1); I am a bit confused as I can't clearly understand why there is a clash; to give you some context, tab1.date is datetime2. Can anyone help me understand what I'm doing wrong please?

declare @report_start date = NULL  

SET @report_start = ISNULL(@report_start , DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)); --If the date it's null, sets it as the first day of the year

Truncate table tab1;

insert tab1
SELECT
    tab2.Date as Date,
    DATENAME(dw,tab2.Date) as [Day of the Week],
    '00' +RIGHT(CONVERT(CHAR(8),DATEADD(second,CAST(REPLACE([Spot Length],' secs','') AS INT),0),108),5) AS [Length of the Spot (Source)] ,
    CASE 
        WHEN '00' +RIGHT(CONVERT(CHAR(8),DATEADD(second,CAST(REPLACE([Spot Length],' secs','') AS INT),0),108),5) in ('0000:01 ','0000:02 ','0000:03 ','0000:04 ','0000:05 ','0000:06 ') THEN '0000:05'
        ELSE '0001:03'
    END as [Updated Length],
    CASE
        WHEN tab2.[Date]='2018-01-01' THEN '2018/1'
        ELSE CAST(YEAR(tab2.[Date]-1) AS VARCHAR(4))+'/'+ cast( DATEPART( wk, tab2.[Date]-1) AS VARCHAR(2))
    END AS [Number of the Week],
    left(datename(month,tab2.[Date]),3)+'-'+ RIGHT(CONVERT(VARCHAR,YEAR(tab2.[Date])),2) AS [month]
from tab2
left join category on tab2.Brand = category.Campaign
where tab2.Date>=@report_start
and tab2.Date <> (select max(date) from tab2)

Upvotes: 0

Views: 777

Answers (1)

Peter B
Peter B

Reputation: 24147

It looks like the first column of tab1 is of type INT, possibly the Primary Key. You didn't tell the INSERT what columns to insert, so the INSERT is expecting to be fed all of tab1s columns in the order that they have been defined. The result is that it tries to put the value of SELECT ... as [DATE] into the INT column.

To overcome this, specify the columns in the INSERT statement:

INSERT tab1 ([Date], [Day of the Week], ...)
SELECT
    tab2.Date as Date,
    DATENAME(dw,tab2.Date) as [Day of the Week],
    ...

If the INT that we are now skipping is indeed the Primary Key of tab1 then this will work if it is using IDENTITY(). If not, then you'll have to find a way to generate suitable PK values in the SELECT.

Upvotes: 4

Related Questions