How do I return a null value for a negative number?

Question

I am trying to perform additional aggregated calculations on a field that I created and because some values are negative, it is messing with the averages. If the values were to be 0, it would also mess with the averages so it's important that these negative values are treated as NULL.

I think that I will probably need the NULLIF calculation on top of my existing query, but the syntax doesn't seem to accept <0 and I can't find anywhere what the proper syntax would be.

SELECT
NULLIF(DATEDIFF(day,deposit_date,acceptance_date),<0) AS 'Days until Acceptance'
FROM data

score 2 · Accepted Answer · answered Jun 17 '19 at 17:56

As suggested above, try:

SELECT
    CASE
        WHEN DATEDIFF(day,deposit_date,[acceptance date]) < 0 THEN NULL
        ELSE DATEDIFF(day,deposit_date,[acceptance date])
    END AS [Days until Acceptance]
FROM data

Also, some of your names do not meet the SQL syntax requirements so these have been changed in my answer.

How do I return a null value for a negative number?

1 Answers1