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

can you show the whole statement? Are you sure it is failing there? Try commenting out this section, will you still get the same error? — Giannis Paraskevopoulos, May 27 '15 at 10:41

score 9 · Accepted Answer · answered May 27 '15 at 10:47

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

Sorry for the delay ughai (months, I'm aware). But finally done! — JoeCool, Oct 15 '15 at 17:18

TSQL NVARCHAR Conversion Error inside CASE statement

1 Answers1