blank instead of zero

Question

Duration = isnull(FunctionA(DateA,DateB),'')

The Function above calculates number of days and if day is null it displays the value 0 instead of blank value

How can I change the above code to so that it shows blank and not 0 for value null?

Answer 1

If your function returns an integer the result from isnull will also be an integer. In the case the return value is null you will have an implicit conversion to integer for '' and that will be 0.

Try this:

declare @xx int
select isnull(@xx,'')

Result:

-----------
0

You can have the space if you first cast the return value from your function to varchar.

declare @xx int
select isnull(cast(@xx as varchar(10)),'')

Result:

----------
                                                                                .

If your function returns 0 instead of null you can use nullif to get a null value before you cast to varchar.

declare @xx int = 0
select isnull(cast(nullif(@xx, 0) as varchar(10)),'')

Summary:

You need this:

Duration = isnull(cast(FunctionA(DateA,DateB) as varchar(10)),'')

or this

Duration = isnull(cast(nullif(FunctionA(DateA,DateB), 0) as varchar(10)),'')