I have the following case statement; I would like it to return But the output I am getting is; I dont want the zeros, I would like the empty string. Answer You want to return a string, so you need to cast the else:

case statement is returning a 0 instead of empty string

I have the following case statement;

case when (
    CASE 
    WHEN datediff(year,view_3.[PERSON BIRTH DATE], @ReportPeriodStartDate)  >= 19 AND view_3.[NI NUMBER] IS NOT NULL 
        THEN '' ELSE view_3.[NI NUMBER]  END ) = 0 then '' else view_3.[NI NUMBER] END AS [NI NUMBER],

I would like it to return

Birthddate  NI Number   

14/04/2012  9000
06/05/2020  2000
01/01/2001
22/12/1998

But the output I am getting is;

Birthddate  NI Number   

14/04/2012  9000
06/05/2020  2000
01/01/2001  0
22/12/1998  0

I dont want the zeros, I would like the empty string.

Answer

You want to return a string, so you need to cast the else:

else cast(view_3.[NI NUMBER] as varchar(255))

Advertisement

Answer