ISNull, NULLIF, COALESCE, not working

Question

I have an SQL query where i'm trying to return a "0" if the result is Null.

So far I have tried:

SELECT     NULLIF (Amountt, '0') AS count
FROM         1Table
WHERE     (Operations_Day BETWEEN @startdate AND @enddate) AND (location = @Storenumber) AND (Hour = '18')

SELECT     ISNULL(Amountt, 0) AS count
FROM         1Table
WHERE     (Operations_Day BETWEEN @startdate AND @enddate) AND (location = @Storenumber) AND (Hour = '18')

SELECT CASE WHEN (Amountt IS NULL) THEN 0 ELSE Amountt END AS count
FROM         1Table
WHERE     (Operations_Day BETWEEN @startdate AND @enddate) AND (location = @Storenumber) AND (Hour = '18')

All results return a BLANK string, which is not handled well in VB.net How can I return "0"

Answer 1

That means that Amountt is not NULL. Try using a case instead:

select (case when Amountt is null or Amountt = 'NULL' or Amountt = '' then 0 else Amountt end)

If the situation is that your code is returning no rows, then you can use aggregation:

SELECT COALESCE(SUM(Amountt), '0') AS count
FROM 1Table
WHERE (Operations_Day BETWEEN @startdate AND @enddate) AND
     (location = @Storenumber) AND (Hour = '18');