how to group datetime into daily/hourly

• Evolving

  Old Hand

  Points: 350

  More actions

  March 22, 2011 at 9:15 pm

  #97746

  If table has column:

  XDateTime

  with Values:

  1/1/11 1:00

  1/1/11 1:15

  1/1/11 1:30

  1/1/11 2:00

  1/1/11 2:30

  etc..

  How do I write SQL to produce distinct hourly values per day:

  1/1/11 1:00

  1/1/11 2:00

  1/1/11 3:00

  so I can AVG the other fields that fall under each given hour's range?

• andrewd.smith

  SSCrazy Eights

  Points: 8444

  More actions

  March 23, 2011 at 2:19 pm

  #1302708

  You can group by the expression:

  DATEADD(hour, DATEDIFF(hour, 0, XDateTime), 0)

  e.g.

  SELECT

  DATEADD(hour, DATEDIFF(hour, 0, XDateTime), 0) AS XHour,

  AVG(XValue) AS XAvg

  FROM MyTable

  GROUP BY DATEADD(hour, DATEDIFF(hour, 0, XDateTime), 0)

  ORDER BY XHour