InstantForum.NET v4.1.4Teradata Forumshttp://www.teradata.com/teradataforum/info@teradata.comThu, 20 Nov 2008 10:48:54 GMT20http://www.teradata.com/teradataforum/Topic11775-9-1.aspxYou could use CALENDAR view :SELECT cal.calendar_date AS avg_dateFROM 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;Thu, 12 Jun 2008 10:02:36 GMTal1_24http://www.teradata.com/teradataforum/Topic11775-9-1.aspxThanks 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.Wed, 11 Jun 2008 15:22:53 GMTywjcjjhttp://www.teradata.com/teradataforum/Topic11775-9-1.aspxwht 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_AFROM(SELECT DATE_A,COUNT(*) AS COUNT_1FROM TEMPGROUP BY 1) TAB_1 (DATE_A,COUNT_1)QUALIFY RANK(COUNT_1 DESC) = 1for mid value calc use :select min(date_a) + ((max(date_a) - min(date_a))/2) from tempWed, 11 Jun 2008 12:30:15 GMTamit_dwhhttp://www.teradata.com/teradataforum/Topic11775-9-1.aspxwhen 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,Mon, 09 Jun 2008 15:08:55 GMTywjcjj