Handling nulls in DATEDIFF calculation

Question

I'm using Microsoft SQL Server 2012 and keep running into the error

Divide by zero error encountered.

for the statement like below. I want to add a null if the two dates are the same.

SELECT 
    sf.TotalPrice / DATEDIFF(day, sf.StartDate,sf.EndDate) as DailyAllocatedRevenue,
    sf.TotalPrice
FROM 
    sftable

score 2 · Answer 1 · edited May 23 '17 at 12:26

2

Use NullIf Function in Sql-Server it Will return null if difference returns 0

See this link will help you

SELECT 

    sf.TotalPrice / NULLIF(DATEDIFF(day, sf.StartDate,sf.EndDate),0) as DailyAllocatedRevenue
    ,sf.TotalPrice

FROM sftable

edited May 23 '17 at 12:26

Community

1
1

answered Aug 23 '16 at 09:25

Dharti Sojitra

207
1
10

score 2 · Accepted Answer · answered Aug 23 '16 at 09:26

2

You can just wrap the DATEDIFF in a NULLIF:

SELECT 

    sf.TotalPrice / NULLIF(DATEDIFF(day, sf.StartDate,sf.EndDate),0) as DailyAllocatedRevenue
    ,sf.TotalPrice

FROM sftable

If any of the inputs are NULL, or the result is 0, it will become NULL and that propagates through the division.

answered Aug 23 '16 at 09:26

Damien_The_Unbeliever

234,701
27
340
448

@Damien, not really sure the OP has read or understood your warning about NULL propagation! – Paul Bambury Aug 23 '16 at 10:24
@PaulBambury - I think the null propagation is actually a part of what they wanted. I could be wrong but I think they wanted the overall result (for `DailyAllocatedRevenue`) to be `NULL` rather than producing a divide by zero error. – Damien_The_Unbeliever Aug 23 '16 at 10:27

score 1 · Answer 3 · edited Aug 23 '16 at 12:13

1

Try and use

case 
     when sd.startdate <> sd.EndDate then sf.TotalPrice / DATEDIFF(day, sf.StartDate, sf.EndDate) as DailyAllocatedRevenue
     else sf.TotalPrice
end

edited Aug 23 '16 at 12:13

Paul Bambury

1,252
1
13
17

answered Aug 23 '16 at 09:26

Rui Filipe Araújo Aguiar

11
2

score 0 · Answer 4 · answered Aug 23 '16 at 09:26

0

IIF(DATEDIFF(day, sf.StartDate,sf.EndDate)=0,NULL, sf.TotalPrice
/Convert(Decimal(18,4),DATEDIFF(day, sf.StartDate,sf.EndDate)))

Should do the trick

answered Aug 23 '16 at 09:26

Szebeni

66
2

Handling nulls in DATEDIFF calculation

4 Answers4