# Calculate AVG on two dates

• ### Question

• Hi,

I would like to calculate AVG on the  difference of two Dates

I have two dates Columns as follows:

- Col1 (Date1)

- Col2 (Date2)

then , i tried to make a query as follows

Select Avg(DateDiff(day,col1,ol2)) as difference

from table1

this will give me an error ..

so,

what's the correct way to find the Avg of the difference of two dates?

Wednesday, November 14, 2012 1:01 PM

• Hi Faisal,

If you trying to use this column in report then you can use Datediff function in report itself instead of calculating in the query, below is the expression to be used for displaying the difference of dates.

=datediff(DateInterval.Day,cdate(Fields!Date.Value),cdate(Fields!Date1.Value)) , as per your requirement you can change DateInterval to month, year etc ..

output will be like as below

Cheers,

Arun Gangumalla

Thursday, November 15, 2012 6:19 AM

### All replies

• What error, it should be working

Select Avg(DateDiff(day,col1,col2)) as difference

from table1

I think you missed a character 'c' for col2, no?

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
MS SQL Consultants: Improves MS SQL Database Performance

Wednesday, November 14, 2012 1:14 PM
• I would like to calculate AVG on the  difference of two Dates

It's unclear to me what your expected results are.  The query you posted will return the average difference of col1 and col2 for the entire table, expressed in days.

If you want to compute the difference in days for each row, just omit the AVG function.

If you want to compute the midpoint of the date range for each row, add a DATEADD:

```SELECT DATEADD(day, DATEDIFF(day, col1, col2)/2, col1) AS difference
FROM dbo.table1;```

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Wednesday, November 14, 2012 1:18 PM
• I just want to calculate Average Difference between two dates in (Days)

When i subtracted col1-col2

it will give me the number of days between two dates , so basically the difference column providing me the number of days,

now, i want to calculate the average of new column (i.e: Difference)

in-short i need AVG(Difference)

hope this will make some understanding..

Wednesday, November 14, 2012 1:24 PM
• In that case, the scrupt you posted should work.  You mentioned an error...what error are you getting?

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Thursday, November 15, 2012 3:07 AM
• Hi Faisalkyo,

Just as Dan said, the query you used will return a column ”Difference” with a single value of average difference date for entire table.

I try to reproduce the issue in my test environment with a similar table, however, it works well. Please share the error message with us, so we can help to work out this issue.

Or, you can try the following query to redesign the report:
SELECT col1,col2, DateDiff(day,col1,col2) as difference
FROM TABLE
And then, in the report design surface, add a new row to the Tablix, and specify the value with following expression:
=Avg(Fields!difference.Value).

Avg Function (Reporting Services)

Regards,
Fanny Liu

Fanny Liu

TechNet Community Support

Thursday, November 15, 2012 3:32 AM
• Hi Faisal,

If you trying to use this column in report then you can use Datediff function in report itself instead of calculating in the query, below is the expression to be used for displaying the difference of dates.

=datediff(DateInterval.Day,cdate(Fields!Date.Value),cdate(Fields!Date1.Value)) , as per your requirement you can change DateInterval to month, year etc ..

output will be like as below