Conversion failed when converting the varchar value 'RESOLVED SATURDAY' to data type int

Question

Good Afternoon,

I'm having a few issues with trying to add text into a existing table, i'm trying to add the text 'resolved saturday' or 'resolved sunday' with the below code:

Insert Into tblwb_tt_RFID_DO (ItemID, ReceiverID, DMFsenderpanellisttypeid, 
DMFReceiverPanellistTypeID, ClassofMailID, DMFCalendarDateofPosting, 
QualityofServiceDateofPosting, DateofReceipt, FirstReadDate, FirstReadTime, 
FirstReadSite, Lastreadday, Lastreaddate, Lastreadtime, LastReadSite, 
ReceivesOnSaturday, ReceiverPostcode, freq, DMFanalysisperiodid, 
EmailAddress, [Action])    
select   d.ItemID,
d.ReceiverId,
d.DMFsenderPanellistTypeID,
d.DMFReceiverPanellistTypeID, 
d.ClassOfMailID,
CAST (d.DMFCalendarDateOfPosting as date) as 'DMFCalendarDateofposting',
CAST (d.QualityOfServiceDateOfPosting as date) as 
'Qualityofservicedateofposting',
CAST (d.DateOfReceipt as date) as 'Dateofreceipt', 
CAST (d.FirstReadDate as date) as 'FirstReadDate',
CAST (d.FirstReadDate as time) as 'FirstReadTime',
d.FirstReadSite,
datename (dw, d.lastreaddate) as 'LastReadDay',
CAST (d.LastReadDate as date) as 'Lastreaddate',
CAST (d.LastReadDate as time) as 'Lastreadtime',
d.LastReadSite,
CASE WHEN d.DMFReceiverPanellistTypeId = 2 then 1
ELSE d.ReceivesOnSaturday
 END as 'ReceivesOnSaturday',
a.PostCode as 'Receiver Postcode',
CASE WHEN ReceiverId is not null then 1
END as 'freq',
   CASE WHEN Datename (dw, d.lastreaddate) = 'Saturday' AND DATEDIFF(dd,DateOfReceipt,LastReadDate) <=2 THEN 'RESOLVED SATURDAY' 
   ELSE CASE WHEN Datename (dw, d.lastreaddate) = 'Sunday' AND DATEDIFF(dd,DateOfReceipt,LastReadDate) <=1 THEN 'RESOLVED SUNDAY' 
   ELSE NULL END 
   END AS 'Action',
d.DMFanalysisperiodid,
p.EmailAddress
from   rmlivecopy..tblrmrsnapshot d
join   rmlivecopy..tblPanellist p
on   p.PanellistId = D.ReceiverId
join   rmlivecopy..tblAddress A
on   A.panellistID = P.panellistID
join   rmlivecopy..tblItemPlanInfo c
on   c.ItemId = d.ItemId AND d.dmfstatusID = 6
left join rmlivecopy..tblitemquery iq on d.itemid = iq.itemid
where   d.DMFSurveysId in (1, 2, 5)
and iq.querycode = 'R1'
and iq.queryuseractionid = 'C'
and iq.queryuseractionid <>  'A'
and   d.firstreadsite is not NULL
and   d.lastreadsite is not NULL
and   LastReadSite LIKE '%B1%'
and   DATEDIFF(dd,DateOfReceipt,LastReadDate) != 0 
and   datepart(hh,LastReadDate) > 6
and   datepart(hh,LastReadDate) < 18
and   d.DMFanalysisperiodid like ('18%')

Now when I try to just select the data, the code works fine, shows me exactly what I want with the records that fall under these conditions being flagged as such in the Action column, but when I try to Insert into the table, I get the error 'Conversion failed when converting the varchar value 'RESOLVED SATURDAY' to data type int.'

I understand I might be having a issue with the case trying to just use the data type int format, but for the life of me I can't think on how to resolve it. Any ideas? I know I should be able to use convert somewhere but I can't think where...

EDIT - Added the Full Insert Statement

The Action Column i'm trying to insert into is Nvarchar(50)

EDIT 2 - I've managed to resolve it, I managed to get the insert statement the wrong way round, adjusted and now working as expected. Thank you guys for the help :)

Post the actual insert statement and the table definition of the table you are inserting into. Your problem is that you are inserting a varchar into a column that's an int. Most common mistake is wrong mapping column, or omitting the column order in the insert statement. — EzLo
You might want to consider whitespace when writing your SQL. Everything being aligned to the left, an easy query to read, does not make. :) — Larnu

Gordon Linoff Gordon Linoff · Accepted Answer · 2018-05-11T11:14:32

Hmmm. There is nothing obvious in your code that would cause this problem, unless the "date" columns contain weird strings (which I doubt).

One typical cause of this problem is when the different values returned by a case expression are of different types. If one is a number, then all are converted to numbers.

I would suggest that you simplify the case expression. There is no need to nest them. This may help fix the error:

(CASE WHEN Datename(dw, d.lastreaddate) = 'Saturday' AND DATEDIFF(day, DateOfReceipt, LastReadDate) <= 2
      THEN 'RESOLVED SATURDAY' 
      WHEN Datename(dw, d.lastreaddate) = 'Sunday' AND DATEDIFF(day, DateOfReceipt, LastReadDate) <= 1
      THEN 'RESOLVED SUNDAY' 
 END) AS Action,

Conversion failed when converting the varchar value 'RESOLVED SATURDAY' to data type int

1 Answers