How to get Avg Date in SQL Qry

问题:

I am trying to get the Average Date in sql and am not sure how to do this. I have the following fields:

ID   Date1             Date2                   Date3
1    05/04/2012        08/09/2012             07/02/2012

i want to add another column that shows the Average Date for the ID number 1. I have done only average number but never done average Date. thanks

回答1:

You could use something like this, which casts the date to a float and then takes the average:

select t.id, t.date1, t.date2, t.date3, src.AvgDate
from yourtable t
inner join 
(
  select id, cast(avg(date) as datetime) AvgDate
  from
  (
    select id, cast(cast(date1 as datetime) as float) date
    from yourtable
    union all
    select id, cast(cast(date2 as datetime) as float) date
    from yourtable
    union all
    select id, cast(cast(date3 as datetime) as float) date
    from yourtable
  ) x
  group by id
) src
  on t.id = src.id;

See SQL Fiddle with Demo

Since your date values are across multiple columns, I performed an unpivot or union all to get the values in a single column first then take the average of that value.

Here is another example of using the UNPIVOT function:

select t.id, t.date1, t.date2, t.date3, src.AvgDate
from yourtable t
inner join 
(
  select id, cast(avg(date) as datetime) AvgDate
  from
  (
    select id, cast(cast(date as datetime) as float) date
    from yourtable
    unpivot
    (
      date
      for col in (date1, date2, date3)
    ) unpiv
  ) x
  group by id
) src
  on t.id = src.id;

See SQL Fiddle with Demo

回答2:

select id, date1, date2, date3
,Cast(
(cast(date1 as Float) + cast(date2 as Float) + cast(date3 as Float))/3
as Datetime) as AvgDate
from yourtable