Getting Conversion failed when converting the varchar value ' --> ' to data type int. How do I solve this?

Question

I made a trigger for when a table is updated. This is a bit of its code:

INSERT INTO [dbo].Logs([date],[name],[changes],[eventId])
             SELECT getdate(), 'john',
             CAST(D.eventStart AS nvarchar(30)) + ' --> ' + CAST(I.eventStart AS nvarchar(30)),
             I.id
             FROM Inserted I
                  INNER JOIN Deleted D ON I.id = D.id

I'm using the CAST because eventStart is of the type dateTime2 and I want it to be nvarchar in this case.

What I'm trying to do is take the 2 dates and the ' --> ' and insert them into a String (changes)

The problem is when I run the application I get:

"Conversion failed when converting the varchar value ' --> ' to data type int."

I'm almost sure the problem has something to do with the CAST line. Any idea why and how to fix this?

PS: changes is of the type nvarchar(MAX).

UPDATE:

All Trigger code:

USE [ITApiContext-20150205100935]
GO
/****** Object:  Trigger [dbo].[EventUpdate]    Script Date: 10-Mar-15 11:55:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================

ALTER TRIGGER [dbo].[EventUpdate]
   ON  [dbo].[Events]
   AFTER UPDATE
AS
BEGIN
       SET NOCOUNT ON;

       INSERT INTO [dbo].Logs([date],[name],[changes],[eventId])
             SELECT getdate(), 'john', 
             D.name + ' --> ' + I.name, 
             I.id
             FROM Inserted I
                  INNER JOIN Deleted D ON I.id = D.id

        INSERT INTO [dbo].Logs([date],[name],[changes],[eventId])
             SELECT getdate(), 'john', 
             D.project + ' --> ' + I.project, 
             I.id
             FROM Inserted I
                  INNER JOIN Deleted D ON I.id = D.id

        INSERT INTO [dbo].Logs([date],[name],[changes],[eventId])
             SELECT getdate(), 'john', 
             D.project + ' --> ' + I.project, 
             I.id
             FROM Inserted I
                  INNER JOIN Deleted D ON I.id = D.id

        INSERT INTO [dbo].Logs([date],[name],[changes],[eventId])
             SELECT getdate(), 'john', 
             D.objectives + ' --> ' + I.objectives, 
             I.id
             FROM Inserted I
                  INNER JOIN Deleted D ON I.id = D.id

        INSERT INTO [dbo].Logs([date],[name],[changes],[eventId])
             SELECT getdate(), 'john', 
             D.city + ' --> ' + I.city, 
             I.id
             FROM Inserted I
                  INNER JOIN Deleted D ON I.id = D.id

        INSERT INTO [dbo].Logs([date],[name],[changes],[eventId])
             SELECT getdate(), 'john', 
             D.country + ' --> ' + I.country, 
             I.id
             FROM Inserted I
                  INNER JOIN Deleted D ON I.id = D.id

        INSERT INTO [dbo].Logs([date],[name],[changes],[eventId])
             SELECT getdate(), 'john', 
             (CAST(D.eventStart AS nvarchar(30)) + N' --> ' + CAST(I.eventStart AS nvarchar(30))), 
             I.id
             FROM Inserted I
                  INNER JOIN Deleted D ON I.id = D.id



        INSERT INTO [dbo].Logs([date],[name],[changes],[eventId])
             SELECT getdate(), 'john', 
             CAST(D.departure AS nvarchar(30)) + N' --> ' + CAST(I.departure AS nvarchar(30)), 
             I.id
             FROM Inserted I
                  INNER JOIN Deleted D ON I.id = D.id

        INSERT INTO [dbo].Logs([date],[name],[changes],[eventId])
             SELECT getdate(), 'john', 
             CAST(D.arrival AS nvarchar(30)) + N' --> ' + CAST(I.arrival AS nvarchar(30)), 
             I.id
             FROM Inserted I
                  INNER JOIN Deleted D ON I.id = D.id

        INSERT INTO [dbo].Logs([date],[name],[changes],[eventId])
             SELECT getdate(), 'john', 
             D.registration + ' --> ' + I.registration, 
             I.id
             FROM Inserted I
                  INNER JOIN Deleted D ON I.id = D.id

        INSERT INTO [dbo].Logs([date],[name],[changes],[eventId])
             SELECT getdate(), 'john', 
             D.nationalTransportation + ' --> ' + I.nationalTransportation, 
             I.id
             FROM Inserted I
                  INNER JOIN Deleted D ON I.id = D.id

        INSERT INTO [dbo].Logs([date],[name],[changes],[eventId])
             SELECT getdate(), 'john', 
             D.accommodation + ' --> ' + I.accommodation, 
             I.id
             FROM Inserted I
                  INNER JOIN Deleted D ON I.id = D.id

        INSERT INTO [dbo].Logs([date],[name],[changes],[eventId])
             SELECT getdate(), 'john', 
             D.acNumberNights + ' --> ' + I.acNumberNights, 
             I.id
             FROM Inserted I
                  INNER JOIN Deleted D ON I.id = D.id

        INSERT INTO [dbo].Logs([date],[name],[changes],[eventId])
             SELECT getdate(), 'john', 
             D.acPreferHotel + ' --> ' + I.acPreferHotel, 
             I.id
             FROM Inserted I
                  INNER JOIN Deleted D ON I.id = D.id

        INSERT INTO [dbo].Logs([date],[name],[changes],[eventId])
             SELECT getdate(), 'john', 
             D.acPreferHotelUrl + ' --> ' + I.acPreferHotelUrl, 
             I.id
             FROM Inserted I
                  INNER JOIN Deleted D ON I.id = D.id

        INSERT INTO [dbo].Logs([date],[name],[changes],[eventId])
             SELECT getdate(), 'john', 
             D.flight + ' --> ' + I.flight, 
             I.id
             FROM Inserted I
                  INNER JOIN Deleted D ON I.id = D.id

        INSERT INTO [dbo].Logs([date],[name],[changes],[eventId])
             SELECT getdate(), 'john', 
             CAST(D.flDeparture AS nvarchar(30)) + N' --> ' + CAST(I.flDeparture AS nvarchar(30)), 
             I.id
             FROM Inserted I
                  INNER JOIN Deleted D ON I.id = D.id

        INSERT INTO [dbo].Logs([date],[name],[changes],[eventId])
             SELECT getdate(), 'john', 
             D.flDepartPrefer + ' --> ' + I.flDepartPrefer, 
             I.id
             FROM Inserted I
                  INNER JOIN Deleted D ON I.id = D.id

        INSERT INTO [dbo].Logs([date],[name],[changes],[eventId])
             SELECT getdate(), 'john', 
             D.flDepartPreferUrl + ' --> ' + I.flDepartPreferUrl, 
             I.id
             FROM Inserted I
                  INNER JOIN Deleted D ON I.id = D.id

        INSERT INTO [dbo].Logs([date],[name],[changes],[eventId])
             SELECT getdate(), 'john', 
             CAST(D.flReturn AS nvarchar(30)) + N' --> ' + CAST(I.flReturn AS nvarchar(30)), 
             I.id
             FROM Inserted I
                  INNER JOIN Deleted D ON I.id = D.id

        INSERT INTO [dbo].Logs([date],[name],[changes],[eventId])
             SELECT getdate(), 'john', 
             D.flRetPrefer + ' --> ' + I.flDepartPrefer, 
             I.id
             FROM Inserted I
                  INNER JOIN Deleted D ON I.id = D.id

        INSERT INTO [dbo].Logs([date],[name],[changes],[eventId])
             SELECT getdate(), 'john', 
             D.flDepartPreferUrl + ' --> ' + I.flDepartPreferUrl, 
             I.id
             FROM Inserted I
                  INNER JOIN Deleted D ON I.id = D.id

        INSERT INTO [dbo].Logs([date],[name],[changes],[eventId])
             SELECT getdate(), 'john', 
             D.notes+ ' --> ' + I.notes, 
             I.id
             FROM Inserted I
                  INNER JOIN Deleted D ON I.id = D.id
END

Are you sure this is the code that is causing the failure? Your types seem to be aligned, according to the information in your question. — Gordon Linoff
I could post the entire trigger if you want but yes I think it has something to do with this code. specifically the CAST line — Vítor Martins
I don't see any problem in that part you have posted, seems to be problem somewhere else. — Stanislovas Kalašnikovas
change it to: CAST(D.eventStart AS nvarchar(30)) + CAST(I.eventStart AS nvarchar(30)) and see what happens. You'll probably still get an error, but possibly closer to the actual problem. — KM.
I've posted all the trigger. PS: I'm bad/new at TSQL so the possibility of a newbie mistake is high — Vítor Martins

Mackan Mackan · Accepted Answer · 2015-03-10T12:36:08

Try this:

(CAST(D.eventStart AS nvarchar(30)) + N' --> ' + CAST(I.eventStart AS nvarchar(30)))

As per the comments, the thing that finally did the trick:

CAST(CAST(D.eventStart AS nvarchar(30)) + '-->' + CAST(I.eventStart AS nvarchar(30)) AS nvarchar(max))

double-casting for the win.

Getting Conversion failed when converting the varchar value ' --> ' to data type int. How do I solve this?

2 Answers