Conversion fails when converting date average to date in sql I have take date average from date and trying to get result of average date but it raising error like: Conversion failed when converting date and/or time from character string.

sql query executed as:

select 
CONVERT
(
    DATE,
    LEFT(
          convert(
                  varchar(10),
                  AVG(
                        convert(
                                 int, 
                                 convert(
                                           varchar(10), 
                                           OnMarkDate, 
                                           112
                                         )
                                 )
                       )
                  ),8
          )
)
from @tbl_XMLResult


Column values are as:

   '2001-10-11',
   '2001-10-03',
   '2001-09-24',
   '2000-05-31',
   '2001-10-15',
   '2008-01-01'


with datatype date.
This query:


gets the number of days between each of your dates and 2000/01/01.
calculate the average number of days
add the average back to 2000/01/01

SELECT 
    DATEADD(DAY
        , AVG(
            DATEDIFF(day, '2000/01/01', OnMarkDate)
        )
        , '2000/01/01' 
    )
FROM @tbl_XMLResult;



Output:

2002-07-30 00:00:00.000


Return type of DATEADD is datetime when a string is used. You can either: 


cast the string to date 
use a variable with a date type
cast the output back to date (ie. remove the time part).


Your data:

DECLARE @tbl_XMLResult TABLE(OnMarkDate date);
INSERT INTO @tbl_XMLResult(OnMarkDate) VALUES
    ('2001-10-11')
   , ('2001-10-03')
   , ('2001-09-24')
   , ('2000-05-31')
   , ('2001-10-15')
   , ('2008-01-01');