Concatenation of varchar-converted DATEDIFF and placing a string afterwards - issues?
This expression is explicitly returning two different data types (nvarchar and int):
(CASE 
    WHEN Trips.CancelDate IS NOT NULL THEN (CONVERT(NVARCHAR(MAX), DATEDIFF(day, Trips.ArrivalDate, Trips.ReturnDate)) + ' ' + 'days')
    ELSE CAST(DATEDIFF(day, Trips.ArrivalDate, Trips.ReturnDate) AS int)
END) AS [Duration]

When CancelDate is not null, the value with be of type nvarchar(max).  Otherwise, it will be of type int.
A column only has one data type, which means SQL Server has to choose between those two and make every row the same type.
int has a higher data type precedence than nvarchar, so SQL Server will implicitly convert the nvarchar values from the first part of your CASE statement to ints.
It fails to convert the strings produced by the first part to int (because they contain letters and spaces), resulting in the error message in your question.
One way to resolve the problem would be to explicitly convert the number to a string, if you want to show both numbers and strings in this column:
(CASE 
    WHEN Trips.CancelDate IS NOT NULL THEN (CONVERT(NVARCHAR(MAX), DATEDIFF(day, Trips.ArrivalDate, Trips.ReturnDate)) + ' days')
    ELSE CAST(DATEDIFF(day, Trips.ArrivalDate, Trips.ReturnDate) AS nvarchar(max))
END) AS [Duration]

As a side note:

I used nvarchar(max) for consistency with the first part of your query.  You should consider how many days are really supported, and reduce the size of the output column

You have 5 characters of fixed length (&quot; days&quot;), so I would expect 7 or 8 characters max would do the trick (for 2 or 3 digit durations - unless your tracking 3+ year trips)
the final data type size can affect things like query memory grants, which will have an impact on performance and concurrency


You probably only need varchar, as it doesn't seem your expression would ever produce unicode data