TSQL NVARCHAR Conversion Error inside CASE statement

Question

This select is driving me crazy.
The error is:

Conversion error converting nvarchar value '17.30 h' to int data type.

Data is:

(DateTime)   (Nvarchar)  (DateTime)
DATAINICI    DATAMANUAL  DATAFI
null         17.30 h     10/01/2015
01/01/2015   20.30 h     null

And the statement is:

CASE WHEN  dbo.Activitat.DataInici is null 
THEN DATEPART(DAY,Activitat.Datafi) 
ELSE CONVERT(NVARCHAR(50), dbo.Activitat.DataManual)
END 

Answer 1

You are getting this error because of implicit conversion. One part of CASE returns NVARCHAR(50) i.e. CONVERT(NVARCHAR(50), dbo.Activitat.DataManual) which cannot be converted into an int and the other returns an int i.e. DATEPART(DAY,Activitat.Datafi).

Something like this will also return the same error.

SELECT CASE WHEN 1=2 THEN 1 ELSE 'errorstring' END

You should CONVERT and return NVARCHAR(50) in both cases so there is no implicit conversion. Something like this.

CASE WHEN  dbo.Activitat.DataInici is null 
THEN CONVERT(NVARCHAR(50),DATEPART(DAY,Activitat.Datafi))
ELSE CONVERT(NVARCHAR(50), dbo.Activitat.DataManual)
END