0

I have a user-defined table-valued function in SQL Server with name dbo.GetBillsByDate that accepts one date parameter @dateOf.

The function returns successfully for all dates in October of 2015 except for 2015 October 14. The exceptional date causes a divide by zero error and also returns a warning. Here is the exact text:

Divide by zero error encountered.
Warning: Null value is eliminated by an aggregate or other SET operation.

That the function runs on every date besides the 14th is confusing to me, but I've found that when I use a variable to pass in the data the function will return successfully. This causes more confusion.

So:

select * from GetBillsByDate('10/14/2015');

returns an error, and:

declare @dateOf date;
set @dateOf = '10/14/2015';
select * from GetBillsByDate(@dateOf);

does not.

Any insight?

Edit: Full body of dbo.GetBillsByDate

create function [dbo].[GetBillsByDate](@dateOf date)
returns table as
return
select propertyID                              PropertyCode, 
       billDate                                DateOf, 
       accountNum                              AccountNumber, 
       address1                                Address1, 
       address2                                Address2,
       dateadd(day, 1-day(@dateOf), @dateOf)   DatePosted,
       glcode                                  LedgerCode, 
       currMonthPostTotal                      AmountPosted

from AccruedBills

where convert(date, dateCreated) = @dateOf 
      and 
      startDate < endDate 
      and 
      billTotal >= 0 
      and 
      currMonthPostTotal<>0
union
select propertyID, 
       billDate, 
       accountNum, 
       address1, 
       address2, 
       dateadd(day, 1, dateadd(month, -1, dateadd(day, 1-day(@dateOf), @dateOf))),
       glcode, 
       prevMonthPostTotal

from AccruedBills

where convert(date, dateCreated) = @dateOf
      and
      prevMonthPostTotal<>0 
      and 
      startDate < endDate 
      and 
      billTotal >= 0
union
select propertyID, 
       billDate, 
       accountNum, 
       address1, 
       address2, 
       dateadd(day, 1, dateadd(month, -1, dateadd(day, 1, dateadd(month, -1, dateadd(day, 1-day(@dateOf), @dateOf))))),
       glcode, 
       remaMonthPostTotal

from AccruedBills

where convert(date, dateCreated) = @dateOf
      and
      remaMonthPostTotal<>0 
      and 
      startDate < endDate 
      and 
      billTotal >= 0

AccruedBills is a view. The query:

select * from AccruedBills where convert(date, dateCreated) = '20151014'

returns successfully.

25
  • 6
    You need to show the body of the GetBillsByDate function. Commented Nov 2, 2015 at 14:41
  • 2
    Does this return an error: declare @ dateOf varchar(10); set @ dateOf = '10/14/2015'; select * from GetBillsByDate(@ dateOf) Commented Nov 2, 2015 at 14:43
  • 1
    You should use the ANSI standard for date strings. 2015-10-14 Commented Nov 2, 2015 at 14:46
  • 2
    I suspect there is something else going on in the query. I find it hard to believe that the date is causing this. Can you post the entire function? Commented Nov 2, 2015 at 14:58
  • 3
    @SeanLange Incidentally, I know we're talking dates here rather than datetimes, but I generally find the non-hyphenated format ('20151014') safer. For example, try SET LANGUAGE italian; SELECT CAST('2015-10-14' AS DATETIME);... Commented Nov 2, 2015 at 15:24

1 Answer 1

1

Problem found in the AccruedBills view. The view uses sequential CTEs that build upon each previous CTE. Two of the queries contain a divide operation. Before these expressions are reached, however, I filter out 0s that would be contained in the divisor column. It seems that what @AdamMartin was saying was the cause. The view was not being evaluated in the order specified each time it was evaluated. By explicitly checking the divisions for divide by 0s, the query completes successfully.

Sign up to request clarification or add additional context in comments.

1 Comment

Yeah, I've had to deal with this a couple of times; it's just your query optimizer taking a different path for some reason. Remember if re-factoring is ever an issue, using a temp table should be a workaround if the view compiles fine when called on its own.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.