Originally posted by dawnh44
datediff('m', {pc_renew;1.EFF_START_DT}, date(year(today), month(today), 01))
oops..did not see that division in the formula ...
a couple of things about this formula...
the second part is overly complex. You do not need the first of the month as it is written. datediff(month) always calculates the difference in the month value regardless of the actual day of the month (e.g datediff('m', date(2011,10,31), date(2011,11,1)) will still return 1 even though it is only 1 day apart).
therefore
datediff('m', {pc_renew;1.EFF_START_DT}, currentdate)
will give you the same result as what your formula and is easier to manage.
now specifically your division by zero issue:
the datediff('m', {pc_renew;1.EFF_START_DT}, currentdate) formula will always get a 0 when the EFF_start_date value is any value during the current month.
Therefore you will alwasy get this error on any date row in your record set where the EEF_start_date is in the current month.
I am not sure how to fix it as I am not sure exactly what you desire to accomplish.
Does that help?
Edited by DBlank - 11 Oct 2011 at 10:58am
IP Logged
