Conversion fails when converting date average to date in sql

Question

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.

Answer 1

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');