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.
SET LANGUAGE italian; SELECT CAST('2015-10-14' AS DATETIME);...