AVG(date_column) overflow

Database
Enthusiast

AVG(date_column) overflow

when I am trying to use the average funtion on a date field, please see the following sql.

select avg(planting_dt) from tablename;

I encountered following error message:

2616: Numeric overflow occurred during computation.

Is there any possible way to do average on a date filed which has a lot of values.

thanks,
3 REPLIES
Enthusiast

Re: AVG(date_column) overflow

wht i can understand is u may require to calculate most recurring date or the mid value of extreme dates.simple function of average wont wrk as two dates can not b added as its only DAYS tht can be added to a date.
for most recurring date use :
SELECT DATE_A
FROM
(
SELECT DATE_A,COUNT(*) AS COUNT_1
FROM TEMP
GROUP BY 1
) TAB_1 (DATE_A,COUNT_1)
QUALIFY RANK(COUNT_1 DESC) = 1
for mid value calc use :
select min(date_a) + ((max(date_a) - min(date_a))/2) from temp

Enthusiast

Re: AVG(date_column) overflow

Thanks for the reply. We worked around this problem. Since all the dates are in one year, I convert it to Julian Date (YYYYDDD) first, then do a average on the last three digit. then convert it back to normal calendar date.
However, if our data set is across years, then I dont know how to deal with it.
Enthusiast

Re: AVG(date_column) overflow

You could use CALENDAR view :

SELECT cal.calendar_date AS avg_date
FROM sys_calendar.calendar AS cal
INNER JOIN
( SELECT AVG(cal.day_of_calendar) AS avg_day
FROM sys_calendar.calendar AS cal
INNER JOIN
tablename AS tbl
ON tbl.planting_dt = cal.calendar_date
) AS tmp
ON tmp.avg_day = cal.day_of_calendar
;