CODEWITHSUNDEEP
sqlsql-serversql-server-2008
vishu
vishu

Reputation: 43

how to handle divide by zero error in sql

cast(CAST(countAta AS float) 
 / CAST(DATEDIFF(day,@searchDate,@EndDate) AS float) as  decimal(16,2)
    )

Upvotes: 2

Views: 11450

Answers (4)

N00b Pr0grammer
N00b Pr0grammer

Reputation: 4647

You can avoid such situations by setting the following parameters before your query and it should work just fine.

SET ARITHABORT OFF 
SET ANSI_WARNINGS OFF

This would return a NULL when you do something like this: 123 / 0

The important point is to set these properties back ON once you are done with such operations. This particularly helps when you have complex queries in your Stored procedure and you don't want to end up writing more and more CASE statements to handle such a situation.

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269923

The best way is NULLIF() . . . but you can't turn the value back into a 0:

select CAST(CAST(countAta AS float) /
            NULLIF(DATEDIFF(day, @searchDate, @EndDate), 0
                  ) as  decimal(16, 2)
           )

This returns NULL if the denominator is 0. Note that you don't have to cast to a float twice.

Upvotes: 0

Maverick Sachin
Maverick Sachin

Reputation: 883

You should always use TRY-CATCH block and use the built-in error handling functions provided by SQL. Also, you can handle it in another way --

SELECT CASE 
    WHEN (CAST(DATEDIFF(Day, @searchDate, @EndDate) AS FLOAT) AS DECIMAL(16, 2)) = 0
        THEN NULL -- Ideally it should return NULL but you can change it as per your requirement
    ELSE CAST(CAST(Counter AS FLOAT) / CAST(DATEDIFF(Day, @searchDate, @EndDate) AS FLOAT) AS DECIMAL(16, 2))
    END

Upvotes: 0

TriV
TriV

Reputation: 5148

You could use NULLIF to avoid devided by zero error. It returns NULL when denominator equals 0

CAST(countAta AS decimal(16,2)) /ISNULL(NULLIF(DATEDIFF(day,@searchDate,@EndDate),0), 1)

Or use CASE WHEN

CAST(countAta AS decimal(16,2)) /
CASE WHEN DATEDIFF(day,@searchDate,@EndDate) = 0 THEN 1 
     ELSE DATEDIFF(day,@searchDate,@EndDate) 
END

Upvotes: -1

Related Questions