1

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.

6

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.